﻿'Description:叶片批次类，批次相关的后台方法
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh 
'create Date:20211018
'Rewriter:
'Rewrite Date:
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports System.Text
Imports Camstar.WebClient.WebServicesProxy.InSiteWebServices
Imports External.APICalling
Imports System.Linq
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Configuration
Imports System.Data.OracleClient

Public Class YPContainerBusiness
    Private m_MaterialObj As New uLMSContainerMaterialBusiness
    Private m_ContainerObj As New uLMSContainerBusiness
    Private m_CommonObj As New uLMSCommonBusiness
    Private m_commissioning As New uLMSCommissioningBusiness
    Private m_CheckPointObj As New uLMSStepCheckPointBusiness
    Private usingImg As String = ConfigurationManager.AppSettings("UsingCheckImageSavePath")
    Private isUseIP As Boolean = IIf(ConfigurationManager.AppSettings("IsUseIP") = "1", True, False)
#Region "打印相关"

    ''' <summary>
    ''' 获取打印相关信息
    ''' add:Wangjh 20211018
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerPrintInfo(ByVal para As Dictionary(Of String, Object)) As DataSet
        Dim result As DataSet = New DataSet()
        Dim containerId As String = para("ContainerId"), workflowId = para("WorkflowId"), beginSequence = "0", productID = ""
        Dim isBlank As Boolean = para("IsBlank")
        Dim strSql = ""
        '批次基本信息
        Dim dtMain As DataTable = New DataTable("dtMain")
        'containerno:批次号包含：热处理批次号(卡片号)+批次编号,Materialtrademark:材料牌号,originalstartdate:建卡日期,Part_RevID:设计图版次，ly2sequence：工艺规程版次
        strSql = <string>select cci.containerid,cci.mfgorderid,cci.mfgordername,cci.containerno,cci.containername,p.Materialtrademark,ut1.attributevalue as BeginStep,c.originalstartdate,fws.sequence beginsequence,
w.Part_RevID,w.ly2sequence,w.Process_No,wb.workflowname,w.workflowrevision,pb.productname,p.productrevision,cci.qty,c.originalqty,c.YpContainerCardNo,c.YpContainerNo,c.YpHeatNo,p.productid,p.charactertype,p.technicalconditions,p.materialspecification,
cci.techniquedate,e.fullname techniqueFullname,c.batchcomments,p.firstusedproduct,c.containercomments,cci.yptemperaconfirmdate,cci.yptemperaempid,te.fullname as yptemperaempName,
cci.checkconfirmdate,cci.checkconfirmempid,ce.fullname as checkconfirmempName,ste.fullname as startemployeeName,cci.yptemperatureinfo,m.lgort
from containercurrentinfo cci
left JOIN mfgorder m ON m.mfgorderid = cci.mfgorderid 
left join product p on p.productid=cci.productid
left join productbase pb on pb.productbaseid=p.productbaseid
 LEFT JOIN userattribute ut1 ON ut1.parentid=cci.containerid AND ut1.userattributename='BeginStep' 
 left join container c on c.containerid=cci.containerid
  LEFT JOIN WORKFLOWSTEP FWS ON FWS.WORKFLOWSTEPNAME=ut1.attributevalue AND FWS.WORKFLOWID='{0}'
left join workflow w on w.workflowid= '{0}'
left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
left join employee e on e.employeeid=cci.techniqueemployeeid
left join employee te on te.employeeid=cci.yptemperaempid
left join employee ce on ce.employeeid=cci.checkconfirmempid
left join employee ste on ste.employeeid = c.startemployeeid
 </string>.Value
        strSql = String.Format(strSql, workflowId)
        strSql += String.Format(" where cci.containerid='{0}' ", containerId)
        dtMain = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtMain.TableName = "dtMain"
        result.Tables.Add(dtMain)
        If dtMain.Rows.Count = 0 Then
            Return result
        Else
            If Not String.IsNullOrWhiteSpace(dtMain.Rows(0)("beginSequence").ToString) Then
                beginSequence = dtMain.Rows(0)("beginSequence").ToString
            End If
            productID = dtMain.Rows(0)("productid").ToString
        End If
        '分卡信息
        strSql = <string>SELECT spi.checkdata,em.fullname,spi.splitcontainername FROM containersplitinfo spi
LEFT JOIN employee em ON em.employeeid = spi.checkemployeeid
WHERE spi.splitcontainername = '{0}'
ORDER BY spi.checkdata DESC</string>.Value
        Dim dtSplit = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, dtMain.Rows(0)("containername").ToString()))
        '如果主卡是分卡，则部分数据取分卡信息
        If dtSplit.Rows.Count > 0 Then
            dtMain.Rows(0)("originalstartdate") = dtSplit.Rows(0)("checkdata").ToString() '开卡日期
            dtMain.Rows(0)("checkconfirmempName") = dtSplit.Rows(0)("fullname").ToString() '检验员（校对员）
            dtMain.AcceptChanges()
        End If

        '批次子序号（edit by zhangrj 20230927 查出所有的子序号，包含报废的）
        Dim childContainer As New DataTable
        strSql = <string>
                    select c.containername,c.status,ws.workflowstepname,REPLACE(c.containername,c2.togetherflag,'') childContainerNo 
                    from container c 
                    LEFT JOIN container c2 ON c2.containerid=c.parentcontainerid
                    LEFT JOIN CheckQtyChangeRecord cqc ON cqc.containerid = c.containerid AND cqc.changeqtytype = 2 AND cqc.isuse = 0 --查询报废记录
                    LEFT JOIN workflowstep ws ON ws.workflowstepid = cqc.workflowstepid
                    where c.parentcontainerid='{0}' and c.status != 0
                    ORDER BY c.containername
                </string>.Value
        strSql = String.Format(strSql, containerId)
        childContainer = DatabaseHelper.Query_ReturnDatTable(strSql)
        childContainer.TableName = "childContainer"
        result.Tables.Add(childContainer)

        ''工序信息

        '查询所有加工信息
        Dim dtContainerStep As New DataTable
        dtContainerStep = DatabaseHelper.Query_ReturnDatTable(GetWorkflowStepForPrint(workflowId, beginSequence)) '工艺下的工序信息

        If isBlank = False Then
            Dim dtContainerStepWorkRecord = DatabaseHelper.Query_ReturnDatTable(GetContainerStepWorkingRecord(containerId, "")) '派报检信息

            'Dim dtContainerShipRecord = DatabaseHelper.Query_ReturnDatTable(GetContainerStepShipWoringRecord(containerId, "")) '转工信息
            'dtContainerStepWorkRecord.Merge(dtContainerShipRecord)

            dtContainerStepWorkRecord.AcceptChanges()

            Dim curContainerStepWorkRecord As DataTable = New DataTable '当前所查工艺的所有加工记录
            Dim drs = dtContainerStepWorkRecord.Select(String.Format("workflowid='{0}'", workflowId))
            If drs.Length > 0 Then
                curContainerStepWorkRecord = drs.CopyToDataTable
            End If
            'Dim tempObj = dtContainerStepWorkRecord.AsEnumerable.Except(curContainerStepWorkRecord.AsEnumerable, DataRowComparer.Default)
            'If tempObj.Count = 0 Then
            '    dtContainerStepWorkRecord.Rows.Clear()
            'Else
            '    dtContainerStepWorkRecord = tempObj.CopyToDataTable
            '    dtContainerStepWorkRecord.DefaultView.Sort = " sequence asc"
            '    dtContainerStepWorkRecord = dtContainerStepWorkRecord.DefaultView.ToTable
            '    dtContainerStepWorkRecord.AcceptChanges() '此时加工记录只剩下当前工艺外所有的
            'End If
            '移除dtContainerStep中和dtContainerStepWorkRecord一样的工序
            Dim delNum = 0
            Dim temDt = dtContainerStep.Copy
            For i As Integer = 0 To temDt.Rows.Count - 1
                If dtContainerStepWorkRecord.Compute("count(containerid)", "workflowstepname='" + temDt.Rows(i)("workflowstepname").ToString + "'") > 0 Then '已有加工记录时移除空白工序
                    dtContainerStep.Rows.RemoveAt(i - delNum)
                    delNum += 1
                ElseIf dtContainerStepWorkRecord.Compute("count(containerid)", "specno = '" + temDt.Rows(i)("specno").ToString + "' and workflowstepname <> '" + temDt.Rows(i)("workflowstepname").ToString + "'") > 0 Then
                    dtContainerStep.Rows.RemoveAt(i - delNum)
                    delNum += 1
                End If
            Next
            '
            dtContainerStep.Merge(dtContainerStepWorkRecord) '加上当前所有工序及加工记录
            dtContainerStep.AcceptChanges()
            dtContainerStep.DefaultView.Sort = " specno asc"
            dtContainerStep = dtContainerStep.DefaultView.ToTable
        End If
        dtContainerStep = GetContainerStepMerge(dtContainerStep) '处理其多条相同记录的工序 add by zhangrj 2021-12-10
        '添加工序是否存在外委 add by zhangrj 2022-06-09
        If Not dtContainerStep.Columns.Contains("ISCOMMISSION") Then
            dtContainerStep.Columns.Add("ISCOMMISSION")
        End If
        Dim dtContainerCommission As DataTable = GetContainerCommissioning(containerId) '批次外委表
        For Each dr As DataRow In dtContainerStep.Rows
            Dim commRows() As DataRow = dtContainerCommission.Select(String.Format("workflowstepname = '{0}'", dr("workflowstepname").ToString))
            '判断是否外委
            If commRows.Length > 0 Then
                dr("ISCOMMISSION") = "1"
            Else
                dr("ISCOMMISSION") = "0"
            End If
        Next

        '添加关键过程批次号 add by zhunan 2022.6.14
        If Not dtContainerStep.Columns.Contains("keyprocessbatchno") Then
            dtContainerStep.Columns.Add("keyprocessbatchno")
        End If
        Dim dtKeyProcessBatchNo As DataTable = GetKeyProcessBatchNo(containerId)
        For Each dr As DataRow In dtContainerStep.Rows
            Dim rows() As DataRow = dtKeyProcessBatchNo.Select(String.Format("workflowstepid = '{0}'", dr("workflowstepid").ToString))
            If rows.Length > 0 Then
                dr("keyprocessbatchno") = rows(0)("keyprocessbatchno")
            End If
        Next

        dtContainerStep.TableName = "dtContainerStep"
        result.Tables.Add(dtContainerStep)

        '获取关键工序工序设备信息，关键过程控制记录
        Dim SpecResource As New DataTable("SpecResource"), dtCheckItemList As New DataTable("dtCheckItemList")
        Dim keySpecs As DataRow() = dtContainerStep.Select("iskey=1")
        strSql = <string>
SELECT t1.*,CASE WHEN t2.resourceid IS NOT NULL THEN 1 ELSE 0 END isused
FROM (
select sr.specid,rd.resourcename,rd.description,rd.resourceid
from SpecResourceInfo sr 
left join resourcedef rd on rd.resourceid=sr.resourceid
where sr.specid in ({0}) 
) t1
LEFT JOIN (
SELECT DISTINCT s.specid,csw.resourceid
FROM containerstepworkreport csw
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csw.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
WHERE csw.isuse = 0 AND csw.containerid = '{1}'
) t2 ON t2.specid = t1.specid AND t2.resourceid = t1.resourceid
</string>.Value
        Dim keySpecIds = "", keyStepIds = ""
        If keySpecs.Length > 0 Then
            For Each keySpec As DataRow In keySpecs
                keySpecIds += "'" + keySpec("specid") + "'" + ","
                keyStepIds += "'" + keySpec("workflowstepid") + "'" + ","
            Next
            keySpecIds = keySpecIds.Trim(",")
            keyStepIds = keyStepIds.Trim(",")
            'SpecResource = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, keySpecIds, containerId)) 'modify zhunan 2022.6.14 关键过程设备型号及编号先暂时停用原先逻辑
            'add zhunan 2022.6.14 关键过程设备型号及编号 先临时查询派工时所选设备
            Dim strSpecResourceSql = <string>
SELECT DISTINCT s.specid,rd.resourceid,rd.resourcename,rd.description,rf.resourcefamilyname,1 AS isused
FROM containerstepworkreport csw
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csw.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN resourcedef rd on rd.resourceid = csw.resourceid
LEFT JOIN Resourcefamily rf ON rf.resourcefamilyid = rd.resourcefamilyid
WHERE csw.isuse = 0 AND csw.containerid = '{0}'
</string>.Value
            SpecResource = DatabaseHelper.Query_ReturnDatTable(String.Format(strSpecResourceSql, containerId))

            '关键过程记录
            If isBlank Then
                dtCheckItemList = DatabaseHelper.Query_ReturnDatTable(GetSQL_StepCheckItemListBlank(New Dictionary(Of String, String) From
                                                                                                           {{"StepIds", keyStepIds}})) ', {"ProductID", productID}
            Else
                dtCheckItemList = DatabaseHelper.Query_ReturnDatTable(GetSQL_StepCheckItemList(New Dictionary(Of String, String) From
                                                                                                            {{"StepIds", keyStepIds}, {"ContainerId", containerId}})) ', {"ProductID", productID}
            End If

            '检测项值转化
            For Each checkItem In dtCheckItemList.Rows 'checkpointnotedis
                'CheckpointnoteDis和DrawingRequireDis是否有值，若没有，则转化后更新 add:Wangjh 20111111
                Dim exuteEntity = New ExcuteEntity("stepcheckpoint", ExcuteEntity.ExcuteTypeEnum.update)
                exuteEntity.WhereFileds = New List(Of FieldEntity) From {New FieldEntity("stepcheckpointid", checkItem("stepcheckpointid").ToString, FieldEntity.FieldTypeEnum.Str)}
                Dim excuteFieldList As New List(Of FieldEntity)
                If String.IsNullOrWhiteSpace(checkItem("CheckpointnoteDis").ToString) Then
                    checkItem("CheckpointnoteDis") = m_CheckPointObj.ConvertTohtml(checkItem("Checkpointnote").ToString, usingImg, isUseIP)
                    excuteFieldList.Add(New FieldEntity("CheckpointnoteDis", checkItem("CheckpointnoteDis").ToString, FieldEntity.FieldTypeEnum.Str))
                End If
                If Not String.IsNullOrWhiteSpace(checkItem("DrawingRequire").ToString) And String.IsNullOrWhiteSpace(checkItem("DrawingRequireDis").ToString) Then
                    checkItem("DrawingRequire") = m_CheckPointObj.ConvertTohtml(checkItem("DrawingRequire").ToString, usingImg, isUseIP)
                    excuteFieldList.Add(New FieldEntity("DrawingRequire", checkItem("DrawingRequire").ToString, FieldEntity.FieldTypeEnum.Str))
                End If
                '更新数据库数据
                If excuteFieldList.Count > 0 Then
                    exuteEntity.ExcuteFileds = excuteFieldList
                    DatabaseAccess.DBUtility.DatabaseHelper.ExecuteDataByEntity(exuteEntity)
                End If
                '
            Next
            dtCheckItemList.AcceptChanges()
        End If
        SpecResource.TableName = "SpecResource"
        result.Tables.Add(SpecResource)
        dtCheckItemList.TableName = "dtCheckItemList"
        result.Tables.Add(dtCheckItemList)
        '批次用户属性
        strSql = String.Format("select ut.userattributename,ut.attributevalue from userattribute ut where ut.parentid='{0}'", containerId)
        Dim containerAttributeDt As New DataTable("containerAttributeDt")
        containerAttributeDt = DatabaseHelper.Query_ReturnDatTable(strSql)
        '多熔炉号的特殊处理
        Dim batchcomments = dtMain.Rows(0)("batchcomments").ToString
        If Not String.IsNullOrWhiteSpace(batchcomments) Then
            If batchcomments.Split(",").Length > 1 Then
                Dim shiFa As Integer = 0
                For Each item In batchcomments.Split(",")
                    If item.Split(":").Length > 1 Then
                        shiFa += Val(item.Split(":")(1))
                    End If
                Next
                Dim row As DataRow() = containerAttributeDt.Select("userattributename='实发数/顺序号'")
                If row.Length > 0 Then
                    containerAttributeDt.Select("dataname='实发数/顺序号'")(0)("attributevalue") = shiFa.ToString
                    containerAttributeDt.Select("dataname='熔炉号母'")(0)("attributevalue") = "见炉号表"
                    containerAttributeDt.Select("dataname='检验编号'")(0)("attributevalue") = "见炉号表"
                End If
                containerAttributeDt.AcceptChanges()
            End If
        End If

        containerAttributeDt.TableName = "containerAttributeDt"
        result.Tables.Add(containerAttributeDt)

        '子工艺流水卡原批次信息
        Dim dtContainerSubContainer = New DataTable()
        strSql = <string>
SELECT cci.productname,cci.togetherflag,cci.containername,cci.containerno,cci.qty
FROM containersubcontainer csc
LEFT JOIN containercurrentinfo cci ON cci.containerid = csc.containerid AND cci.containerstatus = 1
WHERE csc.subcontainerid = '{0}'
                 </string>.Value

        dtContainerSubContainer = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerId))
        dtContainerSubContainer.TableName = "dtContainerSubContainer"
        result.Tables.Add(dtContainerSubContainer)

        '批次温度信息
        Dim dtContainerTemperature = New DataTable()
        strSql = <string>
SELECT yt.temperaturename,yt.temperaturevalue FROM ypcontainertemperatureinfo yt WHERE  yt.containerid = '{0}'
                 </string>.Value

        dtContainerTemperature = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerId))
        dtContainerTemperature.TableName = "dtContainerTemperature"
        result.Tables.Add(dtContainerTemperature)

        '批次工序属性信息 add zhunan 2022.1.19
        Dim dtContainerStepAttributes = New DataTable()
        strSql = "SELECT csa.containerid,csa.workflowstepid,csa.notes FROM containerstepattribute csa WHERE csa.containerid = '{0}'"
        dtContainerStepAttributes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerId))
        dtContainerStepAttributes.TableName = "dtContainerStepAttributes"
        result.Tables.Add(dtContainerStepAttributes)

        '获取批次的子批次信息(热表子批次) add zhangrj 2022-10-29
        Dim dtContainerSubInfo = New DataTable
        strSql = <string>
SELECT Distinct csc.workflowstepid,c.rbtogetherflag FROM containersubcontainer csc
LEFT JOIN container c ON c.containerid = csc.subcontainerid AND c.status = 1
WHERE csc.containerid = '{0}'
                 </string>.Value
        dtContainerSubInfo = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerId))
        dtContainerSubInfo.TableName = "dtContainerSubInfo"
        result.Tables.Add(dtContainerSubInfo)

        '批次工序检验备注信息 add zhangrj 2022.04.24
        'Dim dtContainerStepCheckNotes = New DataTable()
        'strSql = <string>
        '             SELECT cfi.containerid, cfi.checknote,cfi.workflowstepid FROM containerfinishedcheckinfo cfi WHERE cfi.isuse = '0' AND cfi.containerid = '{0}'
        '             UNION ALL
        '             SELECT cci.containerid, cci.checknote,cci.workflowstepid  FROM containercheckinfo cci
        '             WHERE cci.isuse = '0' AND cci.containerid = '{1}'
        '         </string>
        'dtContainerStepCheckNotes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerId, containerId))
        'dtContainerStepCheckNotes.TableName = "dtContainerStepCheckNotes"
        'result.Tables.Add(dtContainerStepCheckNotes)

        '批次集件信息 dtIssue add by zhangrj 2022-03-02
        Dim dtIssue = New DataTable()
        strSql = <string>
                  SELECT o.mfgordername FROM mfgorder o
                  LEFT JOIN container ct ON ct.mfgorderid = o.mfgorderid
                  WHERE ct.containerid = '{0}'
                 </string>.Value
        Dim dtOrder As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containerId))
        strSql = <string>
                   SELECT cl.*,u.attributevalue FROM Collecterpmaterial cl
                   LEFT JOIN container c ON c.containername = cl.productname||'/'||replace(replace(cl.productbatchno,'.',''),' ','')
                   LEFT JOIN Userattribute u ON u.parentid = c.containerid AND u.userattributename = '检验编号'
                   WHERE cl.mfgordername='{0}'
                   ORDER BY cl.createdate DESC
                 </string>.Value
        dtIssue = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, dtOrder.Rows(0)(0).ToString()))
        dtIssue.TableName = "dtIssue"
        result.Tables.Add(dtIssue)
        Return result
    End Function

    ''' <summary>
    ''' 获取待打印的工序信息
    ''' add:Wangjh 20180509
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetWorkflowStepForPrint(ByVal workflowid, ByVal beginSequence) As String
        Dim strSql As String = <string>SELECT w.workflowid,wb.workflowname,ws.workflowstepid,ws.workflowstepname,sb1.specname,s.specid,s.iskey,ws.sequence,ws.subworkflowid,ws.subworkflowbaseid,s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno,NVL(ws.controlcode,s.controlcode) controlcode,
                                ws.steptype,'' reportid,'' firstcheckcontainerno,s.lastcheck FROM workflow w 
                                LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                                LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
                                left join specbase sb on sb.specbaseid = ws.specbaseid 
                                left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
                                left join specbase sb1 on sb1.specbaseid = s.specbaseid 
                                WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) 
                              </string>
        strSql += String.Format(" AND w.workflowid='{0}'", workflowid)
        If Not String.IsNullOrWhiteSpace(beginSequence) Then
            strSql += String.Format(" and ws.sequence>={0}", beginSequence)
        End If
        strSql += " ORDER BY ws.sequence ASC"
        GetWorkflowStepForPrint = strSql
    End Function

    ''' <summary>
    ''' 查询需要打印的转工完成记录
    '''  add：Wangjh 20180508
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetContainerStepShipWoringRecord(ByVal containerID As String, ByVal workflowID As String) As String
        Dim strSql As String = <string>
                                  SELECT csre.containerid,ws.workflowid,s.specid,s.iskey,ws.workflowstepid,ws.workflowstepname,s.opdesc,csre.acceptanceqty PassQty,csre.acceptanceqty ReportQty,e.fullname ReportEmployeeName,e.employeeid reportemployeeid,e.fullname CheckEmployeeName,csre.recivedate reportdate
                                 ,e.employeeid checkemployeeid,(CASE WHEN csre.workflowstepid=ws.workflowstepid and  csre.lossqty!=0 THEN csre.lossqty ELSE NULL END) lossqty,
                                 (CASE WHEN csre.workflowstepid=ws.workflowstepid and csre.defectqty!=0 THEN csre.defectqty ELSE NULL END)  defectqty,'1' usecompleted,ws.sequence,s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno,
 '' reportid,'' firstcheckcontainerno  FROM containershiprecive csre
                                 LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid=csre.containershiprecordid
				                 LEFT JOIN workflowstep ws ON ws.workflowstepid=csrs.workflowstepid   
                                 LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                                 LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                                 LEFT JOIN employee e ON e.employeeid=csre.reciver
				                 LEFT JOIN containersteprecord csr  ON csr.containerid=csre.containerid AND csr.workflowstepid=csrs.workflowstepid
                                WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) AND csre.reciver IS NOT NULL AND csr.containersteprecordid IS NULL
                              </string>
        strSql += String.Format(" and csre.containerid='{0}'", containerID)
        strSql += String.Format(" and ws.workflowid='{0}'", workflowID)
        GetContainerStepShipWoringRecord = strSql
    End Function

    ''' <summary>
    ''' 查询对应批次和工艺的加工记录
    ''' add：Wangjh 20180508
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <param name="workflowID"></param>
    ''' <returns></returns>
    ''' <remarks>and csw.isuse = 0  add by tianFW 2018年9月29日</remarks>
    Private Function GetContainerStepWorkingRecord(ByVal containerID As String, ByVal workflowID As String) As String
        Dim strSql As String = <string>
                                      SELECT csr.containerid,csr.workflowid,csr.workflowstepid,ws.WORKFLOWSTEPname,sb.specname,csr.specid,s.iskey,csr.ReportQty,csr.ReportEmployeeName,csr.reportemployeeid,(CASE WHEN csr.reportemployeeid IS NULL OR csr.reportemployeeid=csr.checkemployeeid THEN csr.checkdate ELSE csr.reportdate END) reportdate,
                                     csw.checktype,csr.checkemployeeid,csr.checkemployeename,nvl(csr.passqty,0) passqty,nvl(csr.lossqty,0) lossqty,nvl(csr.defectqty,0) defectqty,nvl(csr.reworkqty,0) reworkqty,csr.usecompleted,ws.sequence,s.opdesc,
                                     nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno,csw.containerstepworkreportid reportid,
                                     csw.firstcheckcontainerno,s.lastcheck,csw.notes worknote,k.checknote FROM containersteprecord csr 
                                     left join containerstepworkreport csw on csw.containerstepworkreportid=csr.containersteprecordid and csw.isuse = 0 
                                     left join 
                                     ( SELECT t.containerid,t.workflowstepid,wm_concat(t.checknote) checknote FROM 
                                     (SELECT cfi.containerid, cfi.checknote,cfi.workflowstepid FROM containerfinishedcheckinfo cfi 
                                     WHERE cfi.isuse = '0' AND cfi.containerid = '{0}'
                                     UNION ALL
                                     SELECT cci.containerid, cci.checknote,cci.workflowstepid  FROM containercheckinfo cci
                                     WHERE cci.isuse = '0' AND cci.containerid = '{0}'
                                     )t GROUP BY t.containerid,t.workflowstepid)k ON k.containerid = csr.containerid AND k.workflowstepid = csr.workflowstepid 
                                     left join spec s ON s.specid=CSR.SPECID
                                     left join specbase sb on sb.specbaseid=s.specbaseid
                                     LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CSR.WORKFLOWSTEPID
                                     WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) AND csr.Reportdate IS NOT NULL AND csr.usefulstatus=1 
                                     and csr.containerid='{0}'
                               </string>

        strSql = String.Format(strSql, containerID)
        'strSql += String.Format(" and csr.containerid='{0}'", containerID)
        If Not String.IsNullOrWhiteSpace(workflowID) Then
            strSql += String.Format(" and csr.workflowid='{0}'", workflowID)
        End If
        GetContainerStepWorkingRecord = strSql
    End Function

    ''' <summary>
    ''' 关键过程记录信息,不查结果
    ''' Checktype:3设计特性,4:过程参数
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetSQL_StepCheckItemListBlank(ByVal para As Dictionary(Of String, String)) As String
        Dim strSQL =
           <string>
                SELECT distinct SC.NAME CHECKPOINTGROUPNAME,s.SPECID, SC.CHECKPOINTNOTE,
                SC.NAME DESCRIPTION, '' NOTES, '' checkpointresultnote,WFS.CONTROLCODE,SC.ASSEMBLYTYPE,SC.CHECKNO,SC.Checktype,SC.DrawingRequire,sc.drawingrequiredis,
                SC.EMPLOYEEID,SC.INPUTNUM,SC.INTIME,SC.KEYCHECK,SC.MeopCheck,SC.ProductID,SC.QualityCheck,SC.StepCheckPointID,SC.StepInfoID,
                SC.TCCheckPointNote,SC.WorkflowStepID,'' containerstepworkreportid,sc.checkpointnotedis,'' checkpointresultnotedis,'' checkpointresultnotedis_b
              FROM STEPCHECKPOINT SC
              LEFT JOIN WORKFLOWSTEP WFS ON SC.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID
              LEFT JOIN SPECBASE SB  ON WFS.SPECBASEID = SB.SPECBASEID
             left join spec s on s.specid=NVL(SB.REVOFRCDID, WFS.SPECID)
WHERE sc.KeyCheck=1 
            </string>.Value

        If para.ContainsKey("ProductID") Then
            strSQL += String.Format(" and sc.productid='{0}' ", para("ProductID"))
        End If
        If para.ContainsKey("StepIds") Then
            strSQL += String.Format(" and WFS.workflowstepid in ({0}) ", para("StepIds"))
        End If
        strSQL += " ORDER BY SC.CHECKNO"
        Return strSQL
    End Function

    ''' <summary>
    ''' 关键过程记录信息Checktype:3设计特性,4:过程参数
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetSQL_StepCheckItemList(ByVal para As Dictionary(Of String, String)) As String
        Dim strSQL =
            <string>
                SELECT SC.NAME CHECKPOINTGROUPNAME,s.SPECID, SC.CHECKPOINTNOTE,
                SC.NAME DESCRIPTION, '' NOTES, scr.checkpointresultnote,WFS.CONTROLCODE,SC.ASSEMBLYTYPE,SC.CHECKNO,SC.Checktype,SC.DrawingRequire,sc.drawingrequiredis,
                SC.EMPLOYEEID,SC.INPUTNUM,SC.INTIME,SC.KEYCHECK,SC.MeopCheck,SC.ProductID,SC.QualityCheck,SC.StepCheckPointID,SC.StepInfoID,
                SC.TCCheckPointNote,SC.WorkflowStepID,scr.containerstepworkreportid,sc.checkpointnotedis,scr.checkpointresultnotedis,scr.workcheckpointresultnotedis,scr.workcheckpointresultnote
              FROM STEPCHECKPOINT SC
              LEFT JOIN stepcheckpointresult scr ON scr.stepcheckpointid = sc.stepcheckpointid AND scr.containerid = '{0}'
              LEFT JOIN WORKFLOWSTEP WFS ON SC.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID
              LEFT JOIN SPECBASE SB  ON WFS.SPECBASEID = SB.SPECBASEID
             left join spec s on s.specid=NVL(SB.REVOFRCDID, WFS.SPECID)
             LEFT JOIN containerstepworkreport cswr ON cswr.containerstepworkreportid = scr.containerstepworkreportid
            WHERE sc.KeyCheck=1 AND NVL(cswr.checktype,'2') != '1'
            </string>.Value
        strSQL = String.Format(strSQL, para("ContainerId"))
        If para.ContainsKey("ProductID") Then
            strSQL += String.Format(" and sc.productid='{0}' ", para("ProductID"))
        End If
        If para.ContainsKey("StepIds") Then
            strSQL += String.Format(" and WFS.workflowstepid in ({0}) ", para("StepIds"))
        End If

        strSQL += " GROUP BY SC.NAME,s.SPECID, SC.CHECKPOINTNOTE,WFS.CONTROLCODE,SC.ASSEMBLYTYPE,SC.CHECKNO,SC.Checktype,SC.DrawingRequire,sc.drawingrequiredis," &
                "SC.EMPLOYEEID,SC.INPUTNUM,SC.INTIME,SC.KEYCHECK,SC.MeopCheck,SC.ProductID,SC.QualityCheck,SC.StepCheckPointID,SC.StepInfoID," &
               " SC.TCCheckPointNote,SC.WorkflowStepID,scr.checkpointresultnote,scr.containerstepworkreportid,sc.checkpointnotedis,scr.checkpointresultnotedis,scr.workcheckpointresultnotedis,scr.workcheckpointresultnote"

        strSQL += " ORDER BY SC.CHECKNO"
        Return strSQL
    End Function

    ''' <summary>
    ''' 获取批次外委数据 add zhangrj 2022-06-09
    ''' </summary>
    ''' <param name="contianerId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerCommissioning(ByVal contianerId As String) As DataTable
        Dim sql As String = <string>
                            SELECT ws.workflowstepname,cm.workflowstepid FROM containercommissioning cc 
                            LEFT JOIN containercommidetail cm ON cm.containercommissioningid = cc.containercommissioningid AND cm.isuse = 0
                            LEFT JOIN workflowstep ws ON ws.workflowstepid = cm.workflowstepid
                            WHERE cc.containerid = '{0}' and cc.isuse = 0                                    
                            </string>
        sql = String.Format(sql, contianerId)
        Return DatabaseHelper.Query_ReturnDatTable(sql.ToString)
    End Function

    ''' <summary>
    ''' 查询关键过程批次号 add zhunan 2022-06-14
    ''' </summary>
    ''' <param name="contianerId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetKeyProcessBatchNo(ByVal contianerId As String) As DataTable
        Dim sql As String = <string>
SELECT csw.containerid,csw.workflowstepid,csw.keyprocessbatchno
FROM containerstepworkreport csw
WHERE csw.isuse = 0 AND csw.keyprocessbatchno IS NOT NULL AND csw.containerid = '{0}'                       
                            </string>.Value
        sql = String.Format(sql, contianerId)
        Return DatabaseHelper.Query_ReturnDatTable(sql.ToString)
    End Function
#End Region

#Region "先锋批次创建合并"

    ''' <summary>
    ''' 获取开先锋批的批次信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCanStartedContainer(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql = <string>
select cci.containerid,cci.containername,cci.mfgorderid,cci.mfgordername,cci.containertype,DECODE(cCi.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
cci.productname||'/'||cci.containerno productbatch,cci.isdispatch,nvl(cci.workreportnum,0) workreportnum,nvl(cci.dispatchnum,0) dispatchnum,nvl(cci.isship,0) isship,cci.workflowstepid,
nvl(cci.iscommissioning,0) iscommissioning,nvl(cci.ishold,0) ishold,cci.isinstore,cci.containerstatus,nvl(cci.WORKCENTERDISPATCHNUM,0) WORKCENTERDISPATCHNUM,
cci.toworkcenterid,cci.qty,cci.workflowstepname,cci.workflowid,cci.workflowstepsequence,cci.productdescription,cci.productname,c.ypcontainerno,c.ypheatno,c.ypcontainercardno from containercurrentinfo cci
left join container c on c.containerid=cci.containerid
where  cci.isinstore=0 and cci.containerstatus=1  </string>.Value
        strSql += " and not exists (select 1 from PioneerContainerRecord where containerid=cci.containerid) "
        If para.ContainsKey("FactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryID")) Then
                strSql += String.Format(" and cci.originalfactoryid='{0}' ", para("FactoryID"))
            End If
        End If

        If para.ContainsKey("ScannerContainer") Then
            If Not String.IsNullOrWhiteSpace(para("ScannerContainer")) Then
                strSql += String.Format(" and cci.containername='{0}' ", para("ScannerContainer"))
            End If
        End If

        If para.ContainsKey("MfgorderName") Then
            If Not String.IsNullOrWhiteSpace(para("MfgorderName")) Then
                strSql += String.Format(" and cci.MfgorderName like '%{0}%' ", para("MfgorderName"))
            End If
        End If

        If para.ContainsKey("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSql += String.Format(" and cci.ProductName like '%{0}%' ", para("ProductName"))
            End If
        End If

        If para.ContainsKey("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSql += String.Format(" and cci.ContainerName like '%{0}%' ", para("ContainerName"))
            End If
        End If

        If para.ContainsKey("SpecNo") Then
            If Not String.IsNullOrWhiteSpace(para("SpecNo")) Then
                strSql += String.Format(" and cci.SpecNo like '%{0}%' ", para("SpecNo"))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql

        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)

        Return result


    End Function

    ''' <summary>
    ''' 先锋批次创建完后需要更新的一些信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <remarks></remarks>
    Public Sub UpdateContainerAfterXFStarted(ByVal para As Dictionary(Of String, String), ByVal toInfo As ToContainerInfo)
        Dim strSqlList As New ArrayList
        Dim strSql = ""
        '暂停源批次,更新源批次数量
        strSql = <string>update containercurrentinfo set qty=qty-{0} where containerid='{1}'</string>.Value '主卡暂时不暂停,ishold=1,
        strSqlList.Add(String.Format(strSql, para("SplitQty"), para("FromContainerId")))
        '记录先锋批次创建
        strSql = <string>insert into PioneerContainerRecord (Containerid, Createdate,Createemployeeid,Fromcontainerid,Pioneercontainerrecordid, Qty, Toworkflowstepid, Workflowstepid) values ('{0}',sysdate,'{1}','{2}','{3}',{4},'{5}','{6}')</string>.Value
        strSqlList.Add(String.Format(strSql, para("ContainerId"), para("EmployeeId"), para("FromContainerId"), Guid.NewGuid.ToString(), para("SplitQty"), para("ToWorkflowStepId"), para("WorkflowStepId")))
        '派工未报工记录自动更新
        strSql = String.Format(<string>select  csd.containerstepdispatchid from CONTAINERSTEPDISPATCH csd
left join workflowstep ws on ws.workflowstepid=csd.workflowstepid
left join containerstepworkreport csw on csw.containerstepdispatchid=csd.containerstepdispatchid
where csd.containerid='{0}' and ws.workflowid='{1}' and csw.containerstepworkreportid is null</string>.Value, para("FromContainerId"), para("WorkflowId"))
        Dim dispatChDt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dispatChDt.Rows.Count > 0 Then
            Dim dispatchIds As String = ""
            For Each row In dispatChDt.Rows
                dispatchIds += "'" + row("containerstepdispatchid").ToString + "'" + ","
            Next
            dispatchIds = dispatchIds.Trim(",")
            strSql = String.Format("update CONTAINERSTEPDISPATCH csd set csd.qty=CSD.QTY-{0} where csd.containerstepdispatchid in ({1})", para("SplitQty"), dispatchIds)
            strSqlList.Add(strSql)
            strSql = String.Format("update  containersteprecord csr set csr.dispatchqty=csr.dispatchqty-{0} where csr.dispatchid in ({1})", para("SplitQty"), dispatchIds)
            strSqlList.Add(strSql)
        End If
        '更新叶片批次的几个字段
        strSql = String.Format("update container c set c.ypcontainerno='{0}',c.ypheatno='{1}',c.ypcontainercardno='{2}',c.ypcontainertype=3,c.originalstartdate=to_date('{3}','yyyy-mm-dd hh24:mi:ss') where c.containername='{4}' ",
        toInfo.YpContainerNo, toInfo.YpHeatNo, toInfo.YpContainerCardNo, DateTime.Now.ToString(), toInfo.ToContainerName)
        strSqlList.Add(strSql)

        DatabaseHelper.ExecuteSqlTran(strSqlList)
    End Sub

    ''' <summary>
    ''' 先锋批次合并后需要更新的信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <remarks></remarks>
    Public Sub UpdateContainerAfterXFCombined(ByVal para As Dictionary(Of String, String))
        Dim strSqlList As New ArrayList
        Dim strSql = ""
        '释放源批次,更新源批次数量
        strSql = <string>update containercurrentinfo set ishold=0,qty=qty+{0} where containerid='{1}'</string>.Value
        strSqlList.Add(String.Format(strSql, para("SplitQty"), para("FromContainerId")))
        '记录先锋批次合批信息
        strSql = <string>update PioneerContainerRecord set Combinedate=sysdate,Combineemployeeid='{0}',Combineqty={1} where PioneerContainerRecordid='{2}'</string>.Value
        strSqlList.Add(String.Format(strSql, para("EmployeeId"), para("SplitQty"), para("PioneerContainerRecordId")))
        '派工未报工记录自动更新
        strSql = String.Format(<string>select  csd.containerstepdispatchid from CONTAINERSTEPDISPATCH csd
left join workflowstep ws on ws.workflowstepid=csd.workflowstepid
left join containerstepworkreport csw on csw.containerstepdispatchid=csd.containerstepdispatchid
where csd.containerid='{0}' and ws.workflowid='{1}' and csw.containerstepworkreportid is null</string>.Value, para("FromContainerId"), para("WorkflowId"))
        Dim dispatChDt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dispatChDt.Rows.Count > 0 Then
            Dim dispatchIds As String = ""
            For Each row In dispatChDt.Rows
                dispatchIds += "'" + row("containerstepdispatchid").ToString + "'" + ","
            Next
            dispatchIds = dispatchIds.Trim(",")
            strSql = String.Format("update CONTAINERSTEPDISPATCH csd set csd.qty=CSD.QTY+{0} where csd.containerstepdispatchid in ({1})", para("SplitQty"), dispatchIds)
            strSqlList.Add(strSql)
            strSql = String.Format("update  containersteprecord csr set csr.dispatchqty=csr.dispatchqty+{0} where csr.dispatchid in ({1})", para("SplitQty"), dispatchIds)
            strSqlList.Add(strSql)
        End If
        '先锋批次合并完成，自动关闭,数量置为0
        strSql = <string>update containercurrentinfo cci set cci.containerstatus=2,cci.qty=0 where cci.containerid in (select containerid from PioneerContainerRecord where PioneerContainerRecordid='{0}')</string>.Value
        strSqlList.Add(String.Format(strSql, para("PioneerContainerRecordId")))
        DatabaseHelper.ExecuteSqlTran(strSqlList)
    End Sub

    ''' <summary>
    ''' 获取已经创建的先锋批次信息
    ''' </summary>
    ''' <param name="fromContainerId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetPioneerContainerInfo(ByVal fromContainerId) As DataTable
        Dim strSql = <string>select cci.containerid,cci.containername,cci.qty,cci.containerstatus,cci.workflowstepid,cci.workflowstepname,pr.PioneerContainerRecordid,pr.toworkflowstepid,cci.containerstatus from PioneerContainerRecord pr
inner join containercurrentinfo cci on cci.containerid=pr.containerid
where  pr.fromcontainerid='{0}'
order by pr.containerid asc </string>.Value
        Return DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, fromContainerId))
    End Function

    ''' <summary>
    ''' 批次合并
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CombineContainer(ByVal para As ContainerSplitInfo) As ResultStatus
        Dim m_DataList As New List(Of ClientAPIEntity)
        Dim dataEntity As New ClientAPIEntity

        dataEntity = New ClientAPIEntity("Container", InputTypeEnum.Details, DataTypeEnum.ContainerField, para.ContainerName, "", "Lot")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.ContainerField
        m_DataList.Add(dataEntity)

        Dim combineContainerInfo As New Dictionary(Of String, List(Of ClientAPIEntity)) '需要合并的批次
        Dim combineContainerList As New List(Of ClientAPIEntity)

        Dim ToContainerInfo As List(Of ToContainerInfo) = para.ToContainerInfo

        For i As Integer = 0 To ToContainerInfo.Count - 1
            combineContainerList = New List(Of ClientAPIEntity)
            dataEntity = New ClientAPIEntity("FromContainer", InputTypeEnum.Details, DataTypeEnum.ContainerField, ToContainerInfo(i).ToContainerName, "", "Lot")
            combineContainerList.Add(dataEntity)

            dataEntity = New ClientAPIEntity("CloseWhenEmpty", InputTypeEnum.Details, DataTypeEnum.DataField, "1", "")
            combineContainerList.Add(dataEntity)
            If ToContainerInfo(i).Qty IsNot Nothing Then
                If Not String.IsNullOrWhiteSpace(ToContainerInfo(i).Qty.ToString) Then
                    dataEntity = New ClientAPIEntity("Qty", InputTypeEnum.Details, DataTypeEnum.DataField, ToContainerInfo(i).Qty, "")
                    combineContainerList.Add(dataEntity)
                End If

            End If

            combineContainerInfo.Add(i.ToString, combineContainerList)
        Next

        '子序号信息
        Dim dtChildList As DataTable = para.ChildContainer
        Dim strInfo As String = ""
        Dim serverUser As String = para.ServerHostInfo.UserName
        Dim serverPassword As String = para.ServerHostInfo.Password

        '创建批次结束
        Dim result As New ResultStatus
        Dim api As New CamstarClientAPI(serverUser, serverPassword)
        result.IsError = Not api.Combine(m_DataList, combineContainerInfo, dtChildList, strInfo)
        result.Message = strInfo
        Return result

    End Function

    ''' <summary>
    ''' 验证是否存在相同热处理炉号的其他批次 add by zhangrj 20231114
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="msg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsExistsProductBatchNoXFContainer(ByVal dt As DataTable, ByRef msg As String)
        Dim res = False
        Dim sql As String
        For Each item As DataRow In dt.Rows
            sql = <string>
                      SELECT cl.productbatchno FROM container c
                      LEFT JOIN mfgorder m ON m.mfgorderid = c.mfgorderid
                      LEFT JOIN Collecterpmaterial cl ON cl.mfgordername = m.mfgordername
                      WHERE c.containerid = '{0}'
                  </string>.Value
            sql = String.Format(sql, item("containerid").ToString())
            Dim dtRes = DatabaseHelper.Query_ReturnDatTable(sql)
            If dtRes.Rows.Count > 0 Then
                sql = <string>
                      SELECT cci.containername, cci.containerstatus FROM Collecterpmaterial cl 
                      LEFT JOIN mfgorder m ON m.mfgordername = cl.mfgordername
                      LEFT JOIN container c ON c.mfgorderid = m.mfgorderid
                      LEFT JOIN PioneerContainerRecord pcr ON pcr.fromcontainerid = c.containerid
                      LEFT JOIN containercurrentinfo cci ON cci.containerid = pcr.containerid
                      LEFT JOIN container cc ON cc.containerid = cci.containerid
                      WHERE cl.productbatchno = '{0}' AND cc.ypcontainertype = '3'
                      ORDER BY cl.createdate DESC
                  </string>.Value
                sql = String.Format(sql, dtRes.Rows(0)("productbatchno").ToString())
                Dim dtStatus = DatabaseHelper.Query_ReturnDatTable(sql)
                Dim drStatus = dtStatus.AsEnumerable().Where(Function(p) p("containerstatus").ToString() <> "2")
                '如果存在相同热处理炉号的其他先锋批次未合并，则提示不通过
                If drStatus.Count > 0 Then
                    res = True
                    msg = String.Format("存在相同热处理炉号的先锋批次（{0}）未合并", drStatus(0)("containername").ToString())
                    Exit For
                End If
            End If
        Next
        Return res
    End Function

#End Region

#Region "叶片工人报工"

    ''' <summary>
    ''' 查询批次工序设备信息
    ''' </summary>
    ''' <param name="ContainerId"></param>
    ''' <param name="WorkflowStepId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerCurrentSpecResource(ByVal ContainerId As String, ByVal WorkflowStepId As String) As DataTable
        Dim strSql = <string>
SELECT DISTINCT t.resourceid,t.resourcename 
FROM (
SELECT rd.resourceid,rd.resourcename
FROM containerstepdispatch csd
LEFT JOIN Resourcedef rd ON rd.resourceid = csd.resourceid
WHERE csd.isuse = 0 AND csd.containerid = '{0}' AND csd.workflowstepid = '{1}'
UNION ALL 
SELECT rd.resourceid,rd.resourcename
FROM Workflowstep ws 
left join specbase sb on sb.specbaseid = ws.specbaseid 
left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
--left join specbase sb1 on sb1.specbaseid = s.specbaseid
LEFT JOIN specresourceinfo sri ON sri.specid = s.specid
LEFT JOIN Resourcedef rd ON rd.resourceid = sri.resourceid
WHERE ws.workflowstepid = '{1}' 
) t
WHERE t.resourceid IS NOT NULL
 </string>.Value

        strSql = String.Format(strSql, ContainerId, WorkflowStepId)

        GetContainerCurrentSpecResource = DatabaseHelper.Query_ReturnDatTable(strSql)
        Return GetContainerCurrentSpecResource
    End Function

#End Region

#Region "叶片子工艺创建流水卡"
    ''' <summary>
    ''' 获取叶片没有子工艺开卡的信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSubWFProductOfNoContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strsql As New StringBuilder
        Dim strsqlvalue As String = <string>SELECT distinct cu.mfgorderid,cu.mfgordername,cu.productid,cu.productname,cu.productname || '/' || cu.containerno productbatch,c.uomid,u.uomname,cu.containertype,decode(cu.containertype,7,'单件单卡','2','数量批','3','批量带子序号') containertypename,cu.isselfmfg,cu.qty wipqty,cu.productrevision productrev,decode(cu.workflowname,'MW',fwb.workflowname,'MW1',fwb.workflowname,cu.workflowname) workflowname,
decode(cu.workflowname,'MW',c.mfgworkflowid,'MW1',c.mfgworkflowid,cu.workflowid) workflowid,decode(cu.workflowname,'MW',fwf.workflowrevision,'MW1',fwf.workflowrevision,cu.workflowrevision) workflowrev,
sp.productid subproductid,spb.productname subproductname,sp.description subproductdesc,sp.productrevision subproductrev,swf2.workflowid subworkflowid,swf2.process_no subprocessno,swf2.workflowrevision subwrokflowrev,
swb.workflowname subworkflowname,swf2.zgemployeeid techniqueid,em.employeename technique,em.fullname techniquefull,t.teamid,t.teamname,cu.fromworkcenterid workcenterid,cu.fromworkcentername workcentername,c.originalfactoryid,cu.familylistnames,cu.togetherflag,cu.plannedstartdate,
cu.plannedcompletiondate,c.ypcontainerno,c.ypheatno,c.ypcontainercardno,c.ypcontainertype,cu.containerid,cu.workflowstepid,cu.workflowstepname
FROM containercurrentinfo cu
LEFT JOIN container c ON c.containerid = cu.containerid
LEFT JOIN workflow fwf ON fwf.workflowid = c.mfgworkflowid
LEFT JOIN workflowbase fwb ON fwb.workflowbaseid = fwf.workflowbaseid
LEFT JOIN team t ON t.teamid = c.teamid
LEFT JOIN uom u ON u.uomid = c.uomid 
LEFT JOIN (--此情况还要过滤掉子工艺为第二层虚拟工艺情况
SELECT sbs.workflowid,sbs.workflowstepid,sbs.sonworkflowid subworkflowid FROM stepsubstep sbs
left join workflowstep sws on sws.workflowstepid=sbs.sonworkflowstepid--子工艺的工序  
left join specbase sb on sb.specbaseid=sws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,sws.specid)----子工艺的工序
where NVL2(s.commonsubworkflow,'1','0')='0'           
GROUP BY sbs.workflowid,sbs.sonworkflowid,sbs.workflowstepid
union all             
SELECT sbs.workflowid,sbs.workflowstepid,subw.workflowid subworkflowid FROM
(SELECT w.workflowid  FROM workflow w
LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
WHERE w.factoryid='{0}'
GROUP BY w.workflowid
having count(ws.workflowstepid)=1) w--通用工艺情况下，第二层虚拟子工艺 
LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)        
INNER join workflow subw on subw.process_no=s.commonsubworkflow--通用孙子工艺
inner join workflowbase swb on swb.revofrcdid=subw.workflowid
LEFT JOIN stepsubstep sbs ON sbs.sonworkflowid=w.workflowid--第二层虚拟子工艺的关联信息
INNER JOIN workflow fw ON fw.workflowid=sbs.workflowid--第一层工艺，主工艺
WHERE   NVL2(s.commonsubworkflow,'1','0')='1'  
GROUP BY sbs.workflowid,sbs.workflowstepid,subw.workflowid
) cuswf
ON cuswf.workflowid =decode(cu.isinstore,-1,c.mfgworkflowid,cu.workflowid)
LEFT JOIN workflow swf ON swf.workflowid = cuswf.subworkflowid--子工艺
LEFT JOIN workflowbase swb ON swb.workflowbaseid = swf.workflowbaseid --子工艺
left join workflow swf2 on swf2.workflowid=nvl(swf.workflowid,swb.revofrcdid)--子工艺
left join product sp on sp.productid=swf2.productid            
left join productbase spb on spb.productbaseid=sp.productbaseid--子件号            
LEFT JOIN employee em ON em.employeeid = swf2.zgemployeeid
LEFT JOIN product cup ON cup.productid = cu.productid--当前件号
LEFT JOIN producttype pt ON pt.producttypeid = cup.producttypeid
left join ContainerSubContainer csc on csc.subcontainerid=cu.containerid
left join workflowstep ws on ws.workflowstepid=cuswf.workflowstepid
WHERE cu.containerstatus = 1 AND nvl( swf.workflowid,'0')!='0' and nvl(csc.containersubcontainerid,'0')='0'           
AND pt.producttypename!='子工艺虚拟件' </string>.Value 'and  cuswf.workflowstepid=cu.workflowstepid 
        strsqlvalue = String.Format(strsqlvalue, para("factoryID"))
        strsql.AppendLine(strsqlvalue)
        strsql.AppendFormat("  AND swf.factoryid = '{0}' ", para("factoryID"))
        'strsql.AppendLine(" and exists (select 1 from workflowstep ws where ws.workflowstepid=cuswf.workflowstepid and ws.wipmsglabel='子工艺') ")
        strsql.AppendLine(" and ws.wipmsglabel='子工艺' ")

        If para.Keys.Contains("mfgOrderName") Then
            strsql.AppendFormat(" AND cu.mfgordername LIKE '%{0}%' ", para("mfgOrderName"))
        End If
        If para.Keys.Contains("productName") Then
            strsql.AppendFormat(" AND cu.productname LIKE '%{0}%' ", para("productName"))
        End If
        If para.Keys.Contains("childWFName") Then
            strsql.AppendFormat(" AND swb.workflowname LIKE '%{0}%' ", para("childWFName"))
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 叶片保存子工艺流水卡批次映射表
    ''' </summary>
    ''' <param name="chooseDt"></param>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertIntoContainerSubContainer(ByVal chooseDt As DataTable, ByVal para As Dictionary(Of String, String)) As Boolean
        Dim dtContainer As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format("SELECT c.containerid FROM container c WHERE c.containername='{0}'", para("ContainerName")))
        Dim subcontainerid As String = IIf(dtContainer.Rows.Count > 0, dtContainer.Rows(0)("containerid").ToString, "")
        Dim sqlList As ArrayList = New ArrayList()
        Dim insertSql As String = <string>INSERT INTO containersubcontainer (containerid,containersubcontainerid,createdate,createemployeeid,subcontainerid,workflowstepid,faqty,workflowstepname,subworkflowid,subprocessno) 
                                            VALUES('{0}','{1}',SYSDATE,'{2}','{3}','{4}','{5}','{6}','{7}','{8}')
                                  </string>
        For Each row As DataRow In chooseDt.Rows
            sqlList.Add(String.Format(insertSql, row("ContainerID").ToString, Guid.NewGuid().ToString(), para("employeeid"), subcontainerid, row("workflowstepid"), row("wipQty").ToString(), row("WorkflowStepName").ToString(), row("subWorkflowID").ToString(), row("SubProcessNo").ToString))
        Next

        Return DatabaseHelper.ExecuteSqlListTran(sqlList)
    End Function
#End Region

#Region "叶片领料流程相关"
    ''' <summary>
    ''' 获取领料流程主数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerMaterialData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        '非单件单卡查询
        Dim strSql1 = <string>SELECT pt.producttypename,cci.containertype,cci.TogetherFlag,c.mfgworkflowid,cci.productname,cci.productrevision,cci.productid,cci.productdescription description,f.factoryname,cci.specname,cci.specrevision,ws.sequence, cci.workflowstepname,ws.islaststep,cci.specid,cci.workflowstepid,cl.containerlevelname,
cci.qty,cci.isselfmfg,c.materialrequirementdate,c.plannedstartdate, s.roleid,ut3.attributevalue as CheckTechnique,'' AS BeginStep,cci.mfgordername, wb2.WORKFLOWNAME ||':'|| w2.WORKFLOWREVISION  WORKFLOWINFO,
DECODE(Cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,(cci.productname||'/'||cci.containerno) ProductBatchHidden,cci.workflowroute,cci.workflowname||':'||cci.workflowrevision curworkflowinfo
,cci.QTY wipqty,c.factorystartqty sumfactorystartqty,cci.workflowid,p.materialtrademark,w2.usequota
 FROM containercurrentinfo cci 
LEFT JOIN container c ON c.containerid=cci.containerid
        LEFT JOIN containerlevel cl ON cl.containerlevelid=c.levelid
        LEFT JOIN spec s ON s.specid=cci.specid
        LEFT JOIN workflowstep ws ON ws.workflowstepid=cci.workflowstepid
        LEFT JOIN workflow w ON w.workflowid=cci.workflowid
        left join workflow w2 on w2.workflowid=c.mfgworkflowid
        left join workflowbase wb2 on wb2.workflowbaseid=w2.workflowbaseid
        LEFT JOIN cdodefinition cdo ON cdo.cdodefid=w.cdotypeid
        LEFT JOIN factory f ON f.factoryid=cci.originalfactoryid
        LEFT JOIN userattribute ut2 ON ut2.parentid=cci.containerid AND ut2.userattributename='BeginStep' 
        LEFT JOIN userattribute ut3 ON ut3.parentid=cci.containerid AND ut3.userattributename='CheckTechnique' 
        LEFT JOIN product p ON p.productid=cci.productid
        LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid
     WHERE cdo.cdoname='Workflow' AND c.parentcontainerid IS NULL and c.status = 1 
    AND cci.workflowstepname!='完工入库'  and cci.CONTAINERTYPE!=7  </string>.Value
        '单件单卡查询
        Dim strSql2 = <string>SELECT pt.producttypename,cci.containertype,cci.TogetherFlag,c.mfgworkflowid,cci.productname,cci.productrevision,cci.productid,cci.productdescription description,f.factoryname,cci.specname,cci.specrevision,ws.sequence, cci.workflowstepname,ws.islaststep,cci.specid,cci.workflowstepid,cl.containerlevelname,
cci.qty,cci.isselfmfg,c.materialrequirementdate,c.plannedstartdate, s.roleid,ut3.attributevalue as CheckTechnique,'' AS BeginStep,cci.mfgordername, wb2.WORKFLOWNAME ||':'|| w2.WORKFLOWREVISION  WORKFLOWINFO,
DECODE(Cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,(cci.productname||'/'||cci.containerno) ProductBatchHidden,cci.workflowroute,cci.workflowname||':'||cci.workflowrevision curworkflowinfo
 ,sum(cci.QTY) wipqty,sum(c.factorystartqty) sumfactorystartqty,cci.workflowid,p.materialtrademark,w2.usequota
 FROM containercurrentinfo cci 
LEFT JOIN container c ON c.containerid=cci.containerid
        LEFT JOIN containerlevel cl ON cl.containerlevelid=c.levelid
        LEFT JOIN spec s ON s.specid=cci.specid
        LEFT JOIN workflowstep ws ON ws.workflowstepid=cci.workflowstepid
        LEFT JOIN workflow w ON w.workflowid=cci.workflowid
        left join workflow w2 on w2.workflowid=c.mfgworkflowid
        left join workflowbase wb2 on wb2.workflowbaseid=w2.workflowbaseid
        LEFT JOIN cdodefinition cdo ON cdo.cdodefid=w.cdotypeid
        LEFT JOIN factory f ON f.factoryid=cci.originalfactoryid
        LEFT JOIN userattribute ut2 ON ut2.parentid=cci.containerid AND ut2.userattributename='BeginStep' 
        LEFT JOIN userattribute ut3 ON ut3.parentid=cci.containerid AND ut3.userattributename='CheckTechnique' 
        LEFT JOIN product p ON p.productid=cci.productid
        LEFT JOIN producttype pt ON pt.producttypeid=p.producttypeid
     WHERE  c.status = 1 AND c.parentcontainerid IS NULL and cdo.cdoname='Workflow' and cci.containertype=7
    AND cci.workflowstepname!='完工入库' </string>.Value
        Dim groupSql = <string> GROUP BY  pt.producttypename,cci.containertype,cci.TogetherFlag,c.mfgworkflowid,cci.productname,cci.productrevision,cci.productid,cci.productdescription,f.factoryname,cci.specname,cci.specrevision,ws.sequence, cci.workflowstepname,ws.islaststep,cci.specid,cci.workflowstepid,cl.containerlevelname,
cci.qty,cci.isselfmfg,c.materialrequirementdate,c.plannedstartdate, s.roleid,ut3.attributevalue,cci.mfgordername, wb2.WORKFLOWNAME,w2.WORKFLOWREVISION ,
cci.workflowroute,cci.containerno,cci.workflowname,cci.workflowrevision,cci.workflowid,p.materialtrademark,w2.usequota </string>.Value

        Dim condition = ""
        If para.ContainsKey("WorkflowStepName") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowStepName")) Then
                condition += " AND cci.WORKFLOWSTEPNAME='" + para("WorkflowStepName") + "'"
            End If
        End If

        If para.ContainsKey("IsselfMfg") Then
            If Not String.IsNullOrWhiteSpace(para("IsselfMfg")) Then
                condition += " AND cci.IsselfMfg='" + para("IsselfMfg") + "'"
            End If
        End If

        If para.ContainsKey("CheckTechnique") Then
            If Not String.IsNullOrWhiteSpace(para("CheckTechnique")) Then
                condition += " AND ut3.attributevalue='" + para("CheckTechnique") + "'"
            End If
        End If

        If para.ContainsKey("Sequence") Then
            If Not String.IsNullOrWhiteSpace(para("Sequence")) Then
                condition += " AND ws.Sequence=" + para("Sequence")
            End If
        End If

        If para.ContainsKey("WorkcenterID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkcenterID")) Then
                condition += " AND c.WORKCENTERID='" + para("WorkcenterID") + "'"
            End If
        End If

        If para.ContainsKey("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                condition += " AND c.ContainerName like '%" + para("ContainerName") + "%'"
            End If
        End If

        If para.ContainsKey("ScanContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                condition += " AND c.ContainerName = '" + para("ScanContainerName") + "'"
            End If
        End If

        If para.ContainsKey("FactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryID")) Then
                condition += " AND cci.originalfactoryid='" + para("FactoryID") + "'"
            End If
        End If

        If para.ContainsKey("MfgorderName") Then
            If Not String.IsNullOrWhiteSpace(para("MfgorderName")) Then
                condition += " AND cci.MfgorderName like '%" + para("MfgorderName") + "%'"
            End If
        End If

        '最终sql
        Dim strSql = String.Concat(strSql1, condition, " union ", strSql2, condition, groupSql)

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)

    End Function


    ''' <summary>
    ''' 获取领料流程明细数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerMaterialDataDetail(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql = <string> SELECT distinct cci.containername,cci.containerid,cci.productid,cci.productname,cci.qty,c.mfgworkflowid,cci.containertype,cci.workflowstepid,cci.togetherflag,c.factorystartdate,cci.isselfmfg,ut2.attributevalue BeginStep,
wb2.WORKFLOWNAME ||':'|| w2.WORKFLOWREVISION  WORKFLOWINFO,cci.mfgordername,c.ypcontainertype,DECODE(Cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
nvl2(yp.Yptitaniummaterialid,'是','否') istitaniummaterial,nvl2(yt.containerid,'是','否') isHaveTempera,c.ypcontainerno FROM CONTAINERCURRENTINFO CCI
  LEFT JOIN container c ON c.containerid=cci.containerid
        LEFT JOIN workflow w ON w.workflowid=cci.workflowid
         left join workflow w2 on w2.workflowid=c.mfgworkflowid
        left join workflowbase wb2 on wb2.workflowbaseid=w2.workflowbaseid
        LEFT JOIN cdodefinition cdo ON cdo.cdodefid=w.cdotypeid
        LEFT JOIN userattribute ut2 ON ut2.parentid=c.containerid AND ut2.userattributename='BeginStep'   
        LEFT JOIN userattribute ut ON ut.parentid=c.containerid AND ut.userattributename='CloseContianer'
        left join product p on p.productid=cci.productid
        left join YPTitaniumMaterial yp on yp.Materialtrademark=p.productid --钛合金材料
        left join  YPContainerTemperatureInfo yt on yt.containerid=cci.containerid and rownum=1--温度信息
         where cdo.cdoname='Workflow' AND C.STATUS=1   </string>.Value 'and CCI.CONTAINERTYPE!=7

        If para.ContainsKey("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSql += String.Format(" AND CCI.CONTAINERNAME='{0}' ", para("ContainerName"))
            End If
        End If
        If para.ContainsKey("Togetherflag") Then
            If Not String.IsNullOrWhiteSpace(para("Togetherflag")) Then
                strSql += String.Format(" AND CCI.Togetherflag='{0}' ", para("Togetherflag"))
            End If
        End If

        If para.ContainsKey("ProductBatchReal") Then '件号批次号单件单卡为Togetherflag，其他则为containername
            If Not String.IsNullOrWhiteSpace(para("ProductBatchReal")) Then
                strSql += String.Format(" and cci.productname||'/'||cci.containerno='{0}' ", para("ProductBatchReal"))
            End If
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        If dt.Rows.Count > 0 Then
            dt.Columns.Add("SFS")
            dt.Columns.Add("RLHZ")
            dt.Columns.Add("RLHM")
            dt.Columns.Add("JYBH")
            dt.Columns.Add("DLPH")
            dt.Columns.Add("DLGG")
            dt.Columns.Add("DLDH")
            dt.Columns.Add("PXZYGX")
            dt.Columns.Add("PXZYDH")
            dt.Columns.Add("CloseContianer") 'add tianFW 2017/09/08
            dt.Columns.Add("canMove")
            dt.Columns.Add("OrgContainerName") '补卡出来的卡此项是有值的
            Dim strContianerIDList As String = ""
            For Each row As DataRow In dt.Rows
                strContianerIDList = strContianerIDList + "'" + row("containerid").ToString().Trim() + "',"
            Next
            If strContianerIDList.Trim() <> "" Then
                strContianerIDList = strContianerIDList.Substring(0, strContianerIDList.Length - 1)
            End If

            Dim Sql As String = "SELECT c.containername,t.attributevalue,t.userattributename,t.parentid FROM Userattribute t LEFT JOIN container c ON c.containerid=t.parentid WHERE t.parentid IN (" & strContianerIDList & ")"
            Dim sonDataPointTable As DataTable = DatabaseHelper.Query_ReturnDatTable(Sql)

            If sonDataPointTable.Rows.Count > 0 Then
                For Each row As DataRow In dt.Rows
                    AddMaterialInfo(sonDataPointTable, row)
                Next
            End If
            dt.AcceptChanges()
        End If
        '
        Return dt

    End Function

    ''' <summary>
    ''' 添加实发数等信息
    ''' </summary>
    ''' <param name="dataPointTable"></param>
    ''' <param name="row"></param>
    ''' <remarks></remarks>
    Sub AddMaterialInfo(ByVal dataPointTable As DataTable, ByRef row As DataRow)
        Dim strFilter As String = String.Format("parentid='{0}' and userattributename='实发数/顺序号' ", row("containerid").ToString().Trim())
        Dim tmpRows As DataRow() = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("SFS") = tmpRows(0)("attributevalue")
            End If
        End If
        strFilter = String.Format("parentid='{0}' and userattributename='熔炉号子' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("RLHZ") = tmpRows(0)("attributevalue")
            End If
        End If
        strFilter = String.Format("parentid='{0}' and userattributename='熔炉号母' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("RLHM") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='检验编号' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("JYBH") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='代料牌号' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("DLPH") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='代料规格' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("DLGG") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='代料单号' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("DLDH") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='平行作业工序' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("PXZYGX") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='平行作业单号' ", row("containerid").ToString().Trim())
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("PXZYDH") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='CloseContianer' ", row("containerid").ToString().Trim()) 'add tianFW 2017/09/08
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("CloseContianer") = tmpRows(0)("attributevalue")
            End If
        End If

        strFilter = String.Format("parentid='{0}' and userattributename='原批次号' ", row("containerid").ToString().Trim()) 'add tianFW 2017/09/08
        tmpRows = dataPointTable.Select(strFilter)
        If tmpRows.Length > 0 Then
            If tmpRows(0).IsNull("attributevalue") = False Then
                row("OrgContainerName") = tmpRows(0)("attributevalue")
            End If
        End If
    End Sub

    ''' <summary>
    ''' 查询子序号的相关领料信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetChildMaterialInfo(ByVal containerID As String) As DataTable
        Dim strSql As String = <string>
                                  SELECT C.CONTAINERNAME, (substr(c.batchcomments,0,INSTR(c.batchcomments,'实发数：')-1)) AS RLH,(substr(c.batchcomments,INSTR(c.batchcomments,'实发数：')+4)) SFS   FROM container c 
                                  WHERE c.status=1 
                              </string>
        strSql += String.Format(" and c.Parentcontainerid='{0}'", containerID)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetChildMaterialInfo = dt
    End Function

    ''' <summary>
    ''' 批次移动到在制工艺
    ''' </summary>
    ''' <param name="containername"></param>
    ''' <param name="containerlevel"></param>
    ''' <param name="workflowid"></param>
    ''' <param name="beginSetp"></param>
    ''' <param name="userName"></param>
    ''' <param name="password"></param>
    ''' <param name="strInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function MoveMfgWorkFlow(ByVal containername As String, ByVal containerlevel As String, ByVal workflowid As String, ByVal beginSetp As String, ByVal userInfo As CurrentUserInfo, ByRef strInfo As String) As Boolean
        'Ysj:2016-11-23 取消流水卡处于最后一序的限制
        'If Not m_MaterialObj.IsLastStepContainer(containername) Then
        '    Return
        'End If
        'Try
        Dim dt As DataTable = m_MaterialObj.GetFirstWorkflowStep(workflowid)
        Dim res As Boolean = False
        MoveMfgWorkFlow = res
        Dim host As New ServerHostInfo
        host.UserName = userInfo.APIUserName
        host.Password = userInfo.APIPassword

        If dt Is Nothing Then
            strInfo = "流水卡没有在制工艺"
        End If
        If dt.Rows.Count = 0 Then
            strInfo = "流水卡没有在制工艺"
        End If
        Dim api As New CamstarClientAPI(userInfo.APIUserName, userInfo.APIPassword)

        '判断第一个序是否存在检验
        Dim strSql = <string>SELECT 1 FROM containercheckinfo cci
left join container c on c.containerid=cci.containerid
left join workflowstep ws on ws.workflowstepid=cci.workflowstepid
where c.containername='{0}' and ws.sequence=1</string>.Value
        strSql = String.Format(strSql, containername)
        Dim checkInfo As DataTable = DatabaseAccess.DBUtility.DatabaseHelper.Query_ReturnDatTable(strSql)

        If String.IsNullOrWhiteSpace(beginSetp) Then
            res = m_ContainerObj.ContainerMoveNonStd(host, containername, dt.Rows(0)("workflowname"), dt.Rows(0)("workflowrevision"), dt.Rows(0)("workflowstepname"), userInfo.EmployeeName, "", strInfo) 'add:Wangjh 2017-9-4

            If checkInfo.Rows.Count > 0 Then '如果存在第一道工序已检验，则移到下一个工序
                res = m_ContainerObj.ContainerMoveStd(host, containername, userInfo.EmployeeName, strInfo)
            End If
        Else
            res = m_ContainerObj.ContainerMoveNonStd(host, containername, dt.Rows(0)("workflowname"), dt.Rows(0)("workflowrevision"), beginSetp, userInfo.EmployeeName, "", strInfo) 'add:Wangjh 2017-9-4

            If checkInfo.Rows.Count > 0 And dt.Rows(0)("workflowstepname") = beginSetp Then '如果存在第一道工序已检验，则移到下一个工序
                res = m_ContainerObj.ContainerMoveStd(host, containername, userInfo.EmployeeName, strInfo)
            End If
        End If

        If res Then
            strInfo = "操作成功"
        End If

        '以下 tianfw add 2017/06/28 首序固定外委
        Dim newdt As DataTable = m_CommonObj.GetContainerInfobyContainerName(containername)
        Dim para As New Dictionary(Of String, String)
        Dim strOutWorkflowStepName As String = "" '开始序名称
        Dim strOutWorkflowStepID As String = "" '开始序ID
        Dim sequence As String = ""
        para.Add("WorkflowID", workflowid)
        para.Add("Sequence", 1)
        Dim dt_ As DataTable = m_CommonObj.GetWorkFlowStepsWithoutSelf(para) '获取所有的工序信息
        If String.IsNullOrWhiteSpace(beginSetp) Then '根据工序信息获取固定外委的工序Name
            strOutWorkflowStepName = dt.Rows(0)("workflowstepname")
        Else
            strOutWorkflowStepName = beginSetp
        End If
        For Each dr As DataRow In dt_.Rows '获取工序ID和sequence工步
            If dr("WORKFLOWSTEPNAME") = strOutWorkflowStepName Then
                strOutWorkflowStepID = dr("WORKFLOWSTEPID")
                sequence = dr("SEQUENCE")
                Exit For
            End If
        Next
        If m_CommonObj.GetControlCodeByWorkflowStepID(strOutWorkflowStepID) = "LY05" Then '如果不是固定外委，则进行固定转工处理
            para.Clear()
            para.Add("WorkflowID", workflowid)
            para.Add("Sequence", sequence)
            para.Add("WorkflowStepID", strOutWorkflowStepID)
            para.Add("FactoryID", userInfo.FactoryID)
            para.Add("EmployeeID", userInfo.EmployeeID)
            para.Add("OutQty", newdt.Rows(0)("QTY"))
            para.Add("ContainerID", newdt.Rows(0)("CONTAINERID"))
            para.Add("ContainerName", containername)
            m_commissioning.AddFixedCommissionInfo(para)
            'Else 'modify:Wangjh 固定转工统一后台处理
            '    para.Clear()
            '    para.Add("WorkflowID", workflowid)
            '    para.Add("Sequence", sequence)
            '    para.Add("ContainerID", newdt.Rows(0)("CONTAINERID"))
            '    para.Add("WorkFlowStepID", strOutWorkflowStepID)
            '    FixedShip(para)
        End If
        MoveMfgWorkFlow = res
    End Function

    ''' <summary>
    ''' 更改段批号
    ''' add:Wangjh 20211220
    ''' </summary>
    ''' <param name="containerId"></param>
    ''' <remarks></remarks>
    Public Sub ChangeYpContainerNo(ByVal containerId As String, ByVal name As String)

        Dim strSql = String.Format(<string>select pb.productname,c.* from container c 
        left join product p on p.productid=c.productid
        left join productbase pb on pb.productbaseid=p.productbaseid
        where c.containerid='{0}'</string>.Value, containerId)

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        Dim containerNo As String = dt.Rows(0)("ypheatno").ToString + dt.Rows(0)("ypcontainercardno").ToString + name
        Dim newName As String = dt.Rows(0)("productname").ToString() + "/" + containerNo

        Dim sqlArray As New ArrayList()
        Dim updateSql = "update container c set c.containername='{0}' ,c.togetherflag='{1}',c.ypcontainerno='{2}' where c.containerid='{3}' "
        sqlArray.Add(String.Format(updateSql, newName, newName, name, containerId))

        updateSql = "update container c set c.containername=replace(c.containername,'{0}','{1}') where c.parentcontainerid='{2}'"
        sqlArray.Add(String.Format(updateSql, dt.Rows(0)("containername").ToString, newName, containerId))

        updateSql = "update containercurrentinfo set containername='{0}',togetherflag='{1}',containerno='{2}' where containerid='{3}'"
        sqlArray.Add(String.Format(updateSql, newName, newName, containerNo, containerId))

        DatabaseHelper.ExecuteSqlTran(sqlArray)
    End Sub
#End Region

#Region "叶片打印查询工序处理"
    ''' <summary>
    ''' 处理同一个工序有相同检验员和相同报工人的数量有多条时合并 add by zhangrj 2021-12-10
    ''' </summary>
    ''' <param name="dtSourceContainerStep"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerStepMerge(ByVal dtSourceContainerStep As DataTable) As DataTable
        If Not dtSourceContainerStep.Columns.Contains("reportemployeeid") Then
            Return dtSourceContainerStep
        End If
        Dim dtNewContainerStep As DataTable = dtSourceContainerStep.Clone
        Dim clength = dtSourceContainerStep.Columns.Count
        While dtSourceContainerStep.Rows.Count > 0
            Dim item = dtSourceContainerStep.Rows(0)
            Dim rows = dtSourceContainerStep.Select(String.Format("workflowstepid = '{0}' and reportemployeeid='{1}' and checkemployeeid = '{2}'",
                                                                item("workflowstepid").ToString(), item("reportemployeeid").ToString(), item("checkemployeeid").ToString()))
            If rows.Length > 1 Then '如果同一个工序有相同检验员和相同报工人的数量有多条，则合并处理'
                Dim newDr As DataRow = rows(0)
                Dim reportDate As DateTime = DateTime.Parse(rows(0)("reportdate").ToString()) '报工日期
                Dim reportQty As Integer = Integer.Parse(rows(0)("ReportQty").ToString()) '报工数
                Dim passqty As Integer = Integer.Parse(rows(0)("passqty").ToString()) '合格数
                Dim lossqty As Integer = Integer.Parse(rows(0)("lossqty").ToString()) '报废数
                Dim reworkqty As Integer = Integer.Parse(rows(0)("reworkqty").ToString()) '返修数
                Dim defectqty As Integer = 0 '超差数
                Dim firstCheckNo = "" '首检批次号
                Dim worknote As String = rows(0)("worknote").ToString() '工人备注
                If rows.CopyToDataTable.Select("firstcheckcontainerno is not null").Length > 0 Then '首件批次号 add:wangjh 20220114
                    firstCheckNo = rows.CopyToDataTable.Select("firstcheckcontainerno is not null")(0)("firstcheckcontainerno")
                End If
                If Not String.IsNullOrWhiteSpace(rows(0)("defectqty").ToString()) Then
                    defectqty = Integer.Parse(rows(0)("defectqty").ToString())
                End If
                'Dim defectqty As Integer = IIf(String.IsNullOrWhiteSpace(rows(0)("defectqty").ToString()), 0, Integer.Parse(rows(0)("defectqty").ToString()))
                For index = 1 To rows.Length - 1 '从第二行开始。循环每一行'
                    Dim dr = rows(index)
                    '如果报工日期不相同，则不合并
                    If reportDate.ToString("yyyy/MM/dd") <> DateTime.Parse(dr("reportdate").ToString()).ToString("yyyy/MM/dd") Then
                        dtNewContainerStep.Rows.Add(dr.ItemArray)
                        dtSourceContainerStep.Rows.Remove(rows(index))
                        Continue For
                    End If
                    reportQty += Integer.Parse(dr("ReportQty").ToString())
                    passqty += Integer.Parse(dr("passqty").ToString())
                    lossqty += Integer.Parse(dr("lossqty").ToString())
                    reworkqty += Integer.Parse(dr("reworkqty").ToString())
                    If Not String.IsNullOrWhiteSpace(dr("defectqty").ToString()) Then
                        defectqty += Integer.Parse(dr("defectqty").ToString())
                    End If
                    worknote += dr("worknote").ToString() '工人备注
                    'defectqty += IIf(String.IsNullOrWhiteSpace(dr("defectqty").ToString()), 0, Integer.Parse(dr("defectqty").ToString()))
                    For j = 0 To clength - 1 '循环每一列'
                        If Not String.IsNullOrWhiteSpace(dr(j).ToString()) Then
                            newDr(j) = dr(j)
                        End If
                    Next
                    dtSourceContainerStep.Rows.Remove(rows(index))
                Next
                newDr("reportdate") = reportDate
                newDr("ReportQty") = IIf(reportQty = 0, DBNull.Value, reportQty) '如果为0，则不显示出来'
                newDr("passqty") = IIf(passqty = 0, DBNull.Value, passqty)
                newDr("lossqty") = IIf(lossqty = 0, DBNull.Value, lossqty)
                newDr("reworkqty") = IIf(reworkqty = 0, DBNull.Value, reworkqty)
                newDr("defectqty") = IIf(defectqty = 0, DBNull.Value, defectqty)
                newDr("worknote") = worknote
                newDr("firstcheckcontainerno") = firstCheckNo '首件批次号 add:wangjh 20220114
                dtNewContainerStep.Rows.Add(newDr.ItemArray)
                dtSourceContainerStep.Rows.Remove(rows(0))
            Else
                If String.IsNullOrWhiteSpace(item("checkemployeeid").ToString()) Then '如果还没有检验，则不显示详细数据'
                    item("ReportQty") = DBNull.Value
                    item("passqty") = DBNull.Value
                    item("lossqty") = DBNull.Value
                    item("defectqty") = DBNull.Value
                    item("reportdate") = DBNull.Value
                    item("ReportEmployeeName") = ""
                    item("firstcheckcontainerno") = ""
                    item("checkemployeename") = ""
                    If dtNewContainerStep.Select(String.Format("workflowstepid = '{0}'", item("workflowstepid").ToString())).Length = 0 Then '如果没有检验并且还没添加到新表中，则添加'
                        dtNewContainerStep.Rows.Add(item.ItemArray)
                    End If
                Else
                    dtNewContainerStep.Rows.Add(item.ItemArray)
                End If
                dtSourceContainerStep.Rows.Remove(item)
            End If
        End While
        Return dtNewContainerStep
    End Function
#End Region

#Region "叶片钛合金维护"
    ''' <summary>
    ''' 获取钛合金信息列表 add by zhangrj 2021-12-20
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTitaniumInfoForPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strsql As New StringBuilder
        Dim strsqlvalue As String =
            <string>
                SELECT DISTINCT p.materialtrademark,
yt.istitanium,DECODE(yt.istitanium,'0','否','1','是','') istitaniumname,
to_char(yt.createdate,'yyyy-mm-dd') AS createdate,yt.yptitaniummaterialid,e.fullname
 FROM product p 
LEFT JOIN yptitaniummaterial yt ON yt.materialtrademark = p.materialtrademark
LEFT JOIN employee e ON e.employeeid = yt.createemployeeid
WHERE p.materialtrademark IS NOT NULL
            </string>.Value
        strsql.AppendLine(strsqlvalue)
        '查询
        If para.Keys.Contains("Materialtrademark") Then
            strsql.AppendFormat(" AND p.materialtrademark LIKE '%{0}%'", para("Materialtrademark"))
        End If
        If Not String.IsNullOrWhiteSpace(para("IsTitanium").ToString()) Then
            strsql.AppendFormat(" AND yt.istitanium  = '{0}'", para("IsTitanium").ToString())
        End If
        strsql.AppendLine("ORDER BY isTitanium, createdate DESC")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("pageNumber").ToString)
        paraPage.PageSize = Convert.ToInt32(para("pageSize").ToString)
        paraPage.strSQL = strsql.ToString()
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 添加钛合金维护信息 add by zhangrj 2021-12-20
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveTitaniumData(ByVal para As DataTable, ByVal dic As Dictionary(Of String, String)) As uMESProcessResult
        Dim result = New uMESProcessResult()
        Try
            Dim sqlList As New ArrayList
            Dim strsql As New StringBuilder
            For Each item As DataRow In para.Rows
                strsql.Clear()
                If String.IsNullOrWhiteSpace(item("YPTITANIUMMATERIALID").ToString) Then
                    strsql.AppendLine("INSERT INTO yptitaniummaterial (YPTITANIUMMATERIALID,MATERIALTRADEMARK,ISTITANIUM,CREATEEMPLOYEEID,CREATEDATE) VALUES(")
                    strsql.AppendFormat("'{0}',", Guid.NewGuid.ToString)
                    strsql.AppendFormat("'{0}',", item("Materialtrademark"))
                    strsql.AppendFormat("'{0}',", dic("titanium"))
                    strsql.AppendFormat("'{0}',", dic("CreateEmployeeId"))
                    strsql.AppendFormat("{0})", "sysdate")
                Else
                    strsql.AppendFormat("UPDATE yptitaniummaterial yt SET yt.istitanium = '{0}',", dic("titanium"))
                    strsql.AppendFormat("yt.CREATEEMPLOYEEID = '{0}',", dic("CreateEmployeeId"))
                    strsql.AppendLine("yt.CREATEDATE = sysdate")
                    strsql.AppendFormat(" WHERE yt.yptitaniummaterialid = '{1}'", dic("titanium"), item("YPTITANIUMMATERIALID").ToString)
                End If
                sqlList.Add(strsql.ToString)
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Message = "保存成功"
            result.Result = True
        Catch ex As Exception
            result.Message = ex.Message
            result.Result = False
        End Try
        Return result
    End Function
    ''' <summary>
    ''' 是否维护了钛合金（根据零件ID）
    ''' </summary>
    ''' <param name="productId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsTitanium(ByVal productId As String) As Boolean
        IsTitanium = True
        Dim strSql As String =
            <string>
                SELECT yt.*
 FROM yptitaniummaterial yt
LEFT JOIN product p ON p.materialtrademark = yt.materialtrademark
WHERE p.productid = '{0}'
            </string>.Value
        strSql = String.Format(strSql, productId)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
        If dt.Rows.Count = 0 Then
            IsTitanium = False
        End If
    End Function

#End Region

#Region "叶片温度信息处理"
    ''' <summary>
    ''' 获取叶片温度弹窗时批次信息 add by zhangrj 2021-12-20
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerTemperatureInfo(ByVal para As Dictionary(Of String, Object)) As DataTable
        Dim containerId As String = para("ContainerId"), workflowId = para("WorkflowId")
        Dim strSql = ""
        '        strSql = <string>select cci.containerid,cci.mfgorderid,cci.mfgordername,cci.containerno,cci.containername,
        'w.workflowid,wb.workflowname,pb.productname,cci.qty,p.productid,p.firstusedproduct from containercurrentinfo cci 
        'left join product p on p.productid=cci.productid
        'left join productbase pb on pb.productbaseid=p.productbaseid
        'left join workflow w on w.workflowid=p.workflowid
        'left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
        'WHERE w.workflowid = '{0}'
        ' </string>.Value

        strSql = <string>select cci.containerid,cci.mfgorderid,cci.mfgordername,cci.containerno,cci.containername,
'' workflowid,'' workflowname,'' Workflowrevision,pb.productname,cci.qty,p.productid,p.firstusedproduct from containercurrentinfo cci 
left join product p on p.productid=cci.productid
left join productbase pb on pb.productbaseid=p.productbaseid
WHERE cci.containerid='{0}' </string>.Value

        strSql = String.Format(strSql, containerId)

        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        '查工艺
        strSql = <string>select w.workflowid,wb.workflowname,W.WORKFLOWREVISION from workflow w 
left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid where w.workflowid='{0}' </string>.Value

        strSql = String.Format(strSql, workflowId)


        Dim workflowDt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If workflowDt.Rows.Count > 0 Then
            dt.Rows(0)("workflowid") = workflowDt.Rows(0)("workflowid")
            dt.Rows(0)("workflowname") = workflowDt.Rows(0)("workflowname")
            dt.Rows(0)("Workflowrevision") = workflowDt.Rows(0)("Workflowrevision")
        End If
        dt.AcceptChanges()
        Return dt

    End Function

    ''' <summary>
    ''' 获取叶片温度弹窗时批次信息 add:Wangjh 20211224
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerTemperatureInfoBySearch(ByVal para As Dictionary(Of String, Object)) As DataTable

        Dim strSql = ""
        strSql = <string>select cci.containerid,cci.mfgorderid,cci.mfgordername,cci.containerno,cci.containername,
        w.workflowid,W.WORKFLOWREVISION,wb.workflowname,pb.productname,cci.qty,p.productid,p.firstusedproduct from containercurrentinfo cci 
        left join product p on p.productid=cci.productid
        left join productbase pb on pb.productbaseid=p.productbaseid
        left join container c on c.containerid=cci.containerid
        left join workflow w on w.workflowid=decode(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid)
        left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
        {0}
         </string>.Value
        Dim sqlQara = " where cci.containerstatus=1 "
        If para.ContainsKey("Status") Then
            If para("Status").ToString = "1" Then '如果是温度审签中,查询出有温度审签中的批次，add by zhangrj 2022-09-13
                sqlQara = " LEFT JOIN ypcontainertemperatureinfo yp ON yp.containerid = cci.containerid " + sqlQara
                sqlQara += " and yp.temperaturevalue = '温度审签中'"
            End If
        End If
        '附加条件
        strSql = String.Format(strSql, sqlQara)

        If para.ContainsKey("MfgorderName") Then
            If Not String.IsNullOrWhiteSpace(para("MfgorderName")) Then
                strSql += String.Format(" and cci.MfgorderName like '%{0}%' ", para("MfgorderName"))
            End If
        End If

        If para.ContainsKey("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSql += String.Format(" and cci.ContainerName like '%{0}%' ", para("ContainerName"))
            End If
        End If

        If para.ContainsKey("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSql += String.Format(" and cci.ProductName like '%{0}%' ", para("ProductName"))
            End If
        End If

        If para.ContainsKey("FactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryID")) Then
                strSql += String.Format(" and cci.originalfactoryid = '{0}' ", para("FactoryID"))
            End If
        End If

        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)

        Return dt
    End Function


    ''' <summary>
    ''' 通过工艺ID获取工序信息 add by zhangrj 2021-12-20
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkflowStepForTem(ByVal workflowid As String) As DataTable
        Dim strSql As String = <string>SELECT w.workflowid,wb.workflowname,W.WORKFLOWREVISION,ws.workflowstepid,ws.workflowstepname,sb1.specname,s.specid,s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno
                                FROM workflow w 
                                LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                                LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
                                left join specbase sb on sb.specbaseid = ws.specbaseid 
                                left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
                                left join specbase sb1 on sb1.specbaseid = s.specbaseid 
                                WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) 
                              </string>
        strSql += String.Format(" AND w.workflowid='{0}'", workflowid)
        strSql += " ORDER BY ws.sequence ASC"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 获取工序温度 add by zhangrj 2021-12-20
    ''' </summary>
    ''' <param name="containerId"></param>
    ''' <param name="specNo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepTemperatureInfo(ByVal containerId As String, ByVal specNo As String) As DataTable
        Dim strSql As String = <string>SELECT * FROM ypcontainertemperatureinfo yt WHERE yt.containerid = '{0}' AND yt.specno = '{1}'
                              </string>
        strSql = String.Format(strSql, containerId, specNo)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 添加/删除工序温度信息 add by zhangrj 2021-12-20
    ''' </summary>
    ''' <param name="containerId"></param>
    ''' <param name="specNo"></param>
    ''' <param name="specDesc"></param>
    ''' <param name="employeeID"></param>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveStepTemperatureData(ByVal containerId As String, ByVal specNo As String, ByVal specDesc As String, ByVal employeeID As String, ByVal para As List(Of Dictionary(Of String, String))) As Boolean
        Try
            SaveStepTemperatureData = True
            Dim strsql As New StringBuilder
            strsql.AppendFormat("DELETE FROM ypcontainertemperatureinfo ytt WHERE ytt.containerid ='{0}' AND ytt.specno = '{1}' and ytt.specdesc='{2}'", containerId, specNo, specDesc)
            DatabaseHelper.ExecuteSql(strsql.ToString()) '删除之前的记录'

            If Not IsNothing(para) Then
                For Each item As Dictionary(Of String, String) In para
                    strsql.Clear()
                    strsql.AppendLine("INSERT INTO ypcontainertemperatureinfo (ypcontainertemperatureinfoid,CONTAINERID,SPECNO,SPECDESC,TEMPERATURENAME,TEMPERATUREVALUE,CREATEEMPLOYEEID,CREATEDATE) VALUES(sys_guid(),")
                    strsql.AppendFormat("'{0}',", containerId)
                    strsql.AppendFormat("'{0}',", specNo)
                    strsql.AppendFormat("'{0}',", specDesc)
                    strsql.AppendFormat("'{0}',", item("TemName").ToString())
                    strsql.AppendFormat("'{0}',", item("TemValue").ToString())
                    strsql.AppendFormat("'{0}',", employeeID)
                    strsql.AppendFormat("{0})", "sysdate")
                    DatabaseHelper.ExecuteSql(strsql.ToString())
                Next
            End If
        Catch ex As Exception
            SaveStepTemperatureData = False
        End Try
    End Function
#End Region

#Region "首件检验批次号信息处理"
    ''' <summary>
    ''' 获取已经检验首件批次号以及当前批次子序号 add by zhangrj 2021-12-27
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFirstContainerNOInfo(ByVal para As Dictionary(Of String, String)) As DataSet
        Dim result As DataSet = New DataSet()
        Dim strSql As String = <string>
                                   SELECT * FROM
(
SELECT  CSW.FIRSTCHECKCONTAINERNO ,MAX(csw.createdate) MAXcreatedate
FROM CONTAINERSTEPWORKREPORT CSW
LEFT JOIN CONTAINER C ON C.CONTAINERID = CSW.CONTAINERID
WHERE CSW.ISUSE = 0 AND CSW.WORKFLOWSTEPID = '{1}' AND CSW.FIRSTCHECKCONTAINERNO IS NOT NULL AND
CSW.WORKERID = '{2}' AND csw.lossqty is null AND C.PRODUCTID = '{0}'                  
GROUP BY CSW.FIRSTCHECKCONTAINERNO   
ORDER BY   MAX(csw.createdate)    DESC    
) a
 WHERE ROWNUM {3}
                               </string> '取该工序最新前10条的首件批次号
        strSql = String.Format(strSql, para("ProductId"), para("WorkFlowStepId"), para("CurrUserId"), "<=10")
        Dim dtChecked As DataTable = New DataTable()
        dtChecked.TableName = "dtCheckedFirstContainerNo"
        dtChecked = DatabaseHelper.Query_ReturnDatTable(strSql)
        result.Tables.Add(dtChecked)
        'strSql = String.Empty
        'strSql = String.Format("SELECT c.containername FROM container c WHERE c.status=1 AND c.parentcontainerid='{0}'", para("ContainerId"))
        'Dim dtCurrNo As DataTable = New DataTable()
        'dtCurrNo.TableName = "dtCurrFirstContainerNo"
        'dtCurrNo = DatabaseHelper.Query_ReturnDatTable(strSql)
        'result.Tables.Add(dtCurrNo)
        Return result
    End Function
#End Region

#Region "批次属性、批次工序备注维护、配置表属性维护、工步不加工、报工备注维护 add by zhangrj"
    ''' <summary>
    ''' 获取批次工序属性维护的批次信息 add:zhangrj 20220114
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerInfoByStepAttribute(ByVal para As Dictionary(Of String, Object)) As uMESProcessResult
        Dim strSql = <string>select cci.containerid,cci.mfgorderid,cci.mfgordername,cci.containerno,cci.togetherflag,
                    cci.containername,cci.bomid,c.containertype,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
                    ,cci.productdescription,cci.workflowstepid,cci.workflowstepname,decode(cci.isinstore,-1,1,cci.workflowstepsequence) workflowstepsequence,cci.plannedstartdate,cci.plannedcompletiondate,cci.specno,
                    (nvl(cci.Workflowroute,cci.productname)||'/'||cci.containerno) AS ProductBatch,
                    w.workflowid,W.WORKFLOWREVISION,wb.workflowname,pb.productname,cci.qty,p.productid from containercurrentinfo cci 
                    left join product p on p.productid=cci.productid
                    left join productbase pb on pb.productbaseid=p.productbaseid
                    left join container c on c.containerid=cci.containerid
                    left join workflow w on w.workflowid=decode(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid)
                    left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid
                    where cci.containerstatus=1 and cci.isinstore in (-1,0)
         </string>.Value

        If para.ContainsKey("StepNo") Then
            If Not String.IsNullOrWhiteSpace(para("StepNo")) Then
                strSql += String.Format(" and cci.specno like '%{0}%' ", para("StepNo"))
            End If
        End If

        If para.ContainsKey("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSql += String.Format(" and cci.containername like '%{0}%' ", para("ContainerName"))
            End If
        End If

        If para.ContainsKey("ScanContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                strSql += " AND cci.ContainerName = '" + para("ScanContainerName") + "'"
            End If
        End If

        If para.ContainsKey("OrderNo") Then
            If Not String.IsNullOrWhiteSpace(para("OrderNo")) Then
                strSql += String.Format(" and cci.mfgordername like '%{0}%' ", para("OrderNo"))
            End If
        End If

        If para.ContainsKey("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSql += String.Format(" and pb.productname like '%{0}%' ", para("ProductName"))
            End If
        End If

        If para.ContainsKey("FactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryID")) Then
                strSql += String.Format(" and cci.factoryid = '{0}' ", para("FactoryID"))
            End If
        End If
        strSql = "select ck.mfgorderid,ck.mfgordername,ck.containerno,ck.togetherflag,ck.bomid,ck.containertype,ck.CONTAINERTYPENAME" +
 ",ck.productdescription,ck.workflowstepid,ck.workflowstepname,ck.workflowstepsequence,ck.plannedstartdate,ck.plannedcompletiondate,ck.specno, ck.ProductBatch," +
"ck.workflowid,ck.WORKFLOWREVISION,ck.workflowname,ck.productname,ck.qty,ck.productid,sum(ck.qty) sumqty from (" + strSql + ") ck"

        strSql += " group by ck.mfgorderid,ck.mfgordername,ck.containerno,ck.togetherflag,ck.bomid,ck.containertype,ck.CONTAINERTYPENAME" +
 ",ck.productdescription,ck.workflowstepid,ck.workflowstepname,ck.workflowstepsequence,ck.plannedstartdate,ck.plannedcompletiondate,ck.specno, ck.ProductBatch," +
"ck.workflowid,ck.WORKFLOWREVISION,ck.workflowname,ck.productname,ck.qty,ck.productid"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("PageNumber").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 查询多批次详细
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerDetailsInfoByStepAttr(ByVal para As Dictionary(Of String, Object)) As DataTable
        Dim strSql = ""
        strSql = <string>select cci.containerid,cci.mfgorderid,cci.mfgordername,cci.containerno,cci.containername,cci.bomid,c.containertype,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
 ,cci.productdescription,cci.workflowstepid,cci.workflowstepname,cci.plannedstartdate,cci.plannedcompletiondate,cci.specno,
        w.workflowid,W.WORKFLOWREVISION,wb.workflowname,pb.productname,cci.qty,p.productid from containercurrentinfo cci 
        left join product p on p.productid=cci.productid
        left join productbase pb on pb.productbaseid=p.productbaseid
        left join container c on c.containerid=cci.containerid
        left join workflow w on w.workflowid=decode(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid)
        left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid

        where cci.containerstatus=1 and cci.isinstore in (-1,0)</string>

        If para.ContainsKey("OrderNo") Then
            If Not String.IsNullOrWhiteSpace(para("OrderNo")) Then
                strSql += String.Format(" and cci.mfgordername = '{0}' ", para("OrderNo"))
            End If
        End If
        If para.ContainsKey("ProductId") Then
            If Not String.IsNullOrWhiteSpace(para("ProductId")) Then
                strSql += String.Format(" and p.productid = '{0}' ", para("ProductId"))
            End If
        End If

        If para.ContainsKey("Togetherflag") Then
            If Not String.IsNullOrWhiteSpace(para("Togetherflag")) Then
                strSql += String.Format(" and cci.togetherflag = '{0}' ", para("Togetherflag"))
            End If
        End If

        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                strSql += String.Format(" and cci.ContainerNo = '{0}' ", para("ContainerNo"))
            End If
        End If

        If para.ContainsKey("WorkflowRev") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowRev")) Then
                strSql += String.Format(" and cci.workflowrevision = '{0}' ", para("WorkflowRev"))
            End If
        End If

        If para.ContainsKey("WorkflowStepId") Then
            If Not String.IsNullOrWhiteSpace(para("WorkflowStepId")) Then
                strSql += String.Format(" and cci.workflowstepid = '{0}' ", para("WorkflowStepId"))
            End If
        End If


        If para.ContainsKey("FactoryID") Then
            If Not String.IsNullOrWhiteSpace(para("FactoryID")) Then
                strSql += String.Format(" and cci.factoryid = '{0}' ", para("FactoryID"))
            End If
        End If

        strSql += " order by cci.containerid "

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 通过工艺ID、批次ID获取工序属性信息 add by zhangrj 2021-12-20
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkflowStepForSpecAttr(ByVal containerIds As String, ByVal workflowId As String, ByVal sequence As String) As DataTable
        Dim strSql As String = <string>SELECT w.workflowid,wb.workflowname,W.WORKFLOWREVISION,ws.workflowstepid,ws.workflowstepname,sb1.specname,s.specid,s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno
                                ,csa.notes,csa.containerstepattributeid,csa.containerid
                                FROM workflow w 
                                LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                                LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
                                left join specbase sb on sb.specbaseid = ws.specbaseid 
                                left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
                                left join specbase sb1 on sb1.specbaseid = s.specbaseid
                                LEFT JOIN containerstepattribute csa ON csa.workflowstepid = ws.workflowstepid and csa.containerid in ({0})
                                WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL) 
                              </string>
        strSql = String.Format(strSql, containerIds)

        If Not String.IsNullOrWhiteSpace(sequence) Then
            strSql += String.Format(" AND ws.sequence>='{0}'", sequence)
        End If
        strSql += String.Format(" AND w.workflowid='{0}'", workflowId)
        strSql += " ORDER BY ws.sequence ASC"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取工序列表(通过工艺ID) add by zhangrj 2022-3-30
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepListByWorkflowId(ByVal workflowId As String) As DataTable
        Dim strSql As String = <string>SELECT w.workflowid,wb.workflowname,W.WORKFLOWREVISION,ws.workflowstepid,ws.workflowstepname,ws.sequence,sb1.specname,s.specid,s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno
                                FROM workflow w 
                                LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid
                                LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
                                left join specbase sb on sb.specbaseid = ws.specbaseid 
                                left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
                                left join specbase sb1 on sb1.specbaseid = s.specbaseid
                                WHERE (NVL(ws.controlcode,s.controlcode)!='LY07' OR NVL(ws.controlcode,s.controlcode) IS NULL)
                                AND w.workflowid = '{0}'
                              </string>
        strSql = String.Format(strSql, workflowId)
        strSql += " ORDER BY ws.sequence ASC"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取不加工工序列表(通过多个批次id拼接串) add by zhangrj 2022-3-30
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepNoShipList(ByVal sContainerId As String) As DataTable
        Dim strSql As String = <string>
SELECT css.*,e.fullname,cci.containername,s.opdesc,nvl(s.specno,SUBSTR(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1)) specno
,ws.sequence
FROM containerstepnoship css
LEFT JOIN containercurrentinfo cci ON cci.containerid = css.containerid
LEFT JOIN employee e ON e.employeeid = css.employeeid
LEFT JOIN workflowstep ws ON ws.workflowstepid = css.workflowstepid
left join specbase sb on sb.specbaseid = ws.specbaseid 
left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
WHERE css.noship = '2' AND nvl(css.isuse,0) = 0 AND css.containerid IN ({0})
                              </string>
        strSql = String.Format(strSql, sContainerId)
        strSql += " ORDER BY css.containerid, specno asc"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 保存批次属性 add by zhangrj 2022-01-18
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveContainerAttribute(ByVal para As List(Of Dictionary(Of String, String))) As Boolean
        Try
            SaveContainerAttribute = True
            For Each item As Dictionary(Of String, String) In para
                Dim strsql As New StringBuilder
                strsql.Clear()
                strsql.AppendFormat("UPDATE Userattribute ut set ut.attributevalue = '{0}' where ut.parentid='{1}' and  ut.userattributename = '器材检验'", item("EquipmentCheck"), item("ContainerId"))
                DatabaseHelper.ExecuteSql(strsql.ToString())
                strsql.Clear()
                strsql.AppendFormat("UPDATE Userattribute ut set ut.attributevalue = '{0}' where ut.parentid='{1}' and  ut.userattributename = '顾客代表'", item("CustomDep"), item("ContainerId"))
                DatabaseHelper.ExecuteSql(strsql.ToString())
            Next
        Catch ex As Exception
            SaveContainerAttribute = False
        End Try
    End Function

    ''' <summary>
    ''' 保存/清除批次工序属性  add by zhangrj 2022-01-14
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveContainerStepAttribute(ByVal para As List(Of Dictionary(Of String, String))) As Boolean
        Try
            Dim sqlList As New ArrayList
            SaveContainerStepAttribute = True
            For Each item As Dictionary(Of String, String) In para
                Dim strsql As New StringBuilder
                strsql.Clear()
                If item.Keys.Contains("ContainerStepAttributeId") Then '如果是单批次操作
                    If item("isClear").ToString = "1" Then '删除
                        strsql.AppendFormat("DELETE FROM containerstepattribute csa where csa.CONTAINERSTEPATTRIBUTEID='{0}'", item("ContainerStepAttributeId"))
                    ElseIf String.IsNullOrWhiteSpace(item("ContainerStepAttributeId").ToString) Then '添加
                        strsql.AppendLine("INSERT INTO containerstepattribute (CONTAINERSTEPATTRIBUTEID,CONTAINERID,WORKFLOWSTEPID,NOTES,CREATEEMPLOYEEID,CREATEDATE) VALUES(sys_guid(),")
                        strsql.AppendFormat("'{0}',", item("ContainerId"))
                        strsql.AppendFormat("'{0}',", item("WorkFlowStepId"))
                        strsql.AppendFormat("'{0}',", item("Notes"))
                        strsql.AppendFormat("'{0}',", item("CreateEmployeeId"))
                        strsql.AppendFormat("{0})", "sysdate")
                    Else '更新
                        strsql.AppendFormat("UPDATE containerstepattribute csa set csa.NOTES ='{0}' where csa.CONTAINERSTEPATTRIBUTEID='{1}'", item("Notes"), item("ContainerStepAttributeId"))
                    End If
                    sqlList.Add(strsql.ToString)
                Else '多批次操作
                    If item("isClear").ToString = "1" Then
                        strsql.AppendFormat("DELETE FROM containerstepattribute csa where csa.containerid = '{0}' AND csa.workflowstepid = '{1}' AND csa.notes = '{2}'", item("ContainerId"), item("WorkFlowStepId"), item("OldNotes"))
                    Else
                        Dim str = "SELECT c.containerstepattributeid FROM containerstepattribute c WHERE c.containerid = '{0}' AND c.workflowstepid = '{1}' AND c.notes = '{2}'"
                        str = String.Format(str, item("ContainerId"), item("WorkFlowStepId"), item("OldNotes"))
                        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(str.ToString())
                        If dt.Rows.Count > 0 Then
                            strsql.AppendFormat("UPDATE containerstepattribute csa set csa.NOTES ='{0}' where csa.CONTAINERSTEPATTRIBUTEID='{1}'", item("Notes"), dt.Rows(0)("ContainerStepAttributeId"))
                        Else
                            strsql.AppendLine("INSERT INTO containerstepattribute (CONTAINERSTEPATTRIBUTEID,CONTAINERID,WORKFLOWSTEPID,NOTES,CREATEEMPLOYEEID,CREATEDATE) VALUES(sys_guid(),")
                            strsql.AppendFormat("'{0}',", item("ContainerId"))
                            strsql.AppendFormat("'{0}',", item("WorkFlowStepId"))
                            strsql.AppendFormat("'{0}',", item("Notes"))
                            strsql.AppendFormat("'{0}',", item("CreateEmployeeId"))
                            strsql.AppendFormat("{0})", "sysdate")
                        End If
                    End If
                    sqlList.Add(strsql.ToString)
                End If
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
        Catch ex As Exception
            SaveContainerStepAttribute = False
        End Try
    End Function

    ''' <summary>
    ''' 保存/清除配置表属性  add by zhangrj 2022-01-18
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveContainerBomProductAttribute(ByVal para As List(Of Dictionary(Of String, String))) As Boolean
        Try
            SaveContainerBomProductAttribute = True
            For Each item As Dictionary(Of String, String) In para
                Dim strsql As New StringBuilder
                strsql.Clear()
                If item("isClear").ToString = "1" Then '如果是清除
                    strsql.AppendFormat("DELETE FROM containerbomproductattr cbd where cbd.CONTAINERID='{0}' and cbd.PRODUCTID='{1}'", item("ContainerId"), item("ProductId"))
                Else
                    If item("IsBomProduct").ToString = "0" Then
                        strsql.AppendLine("INSERT INTO containerbomproductattr (CONTAINERBOMPRODUCTATTRID,CONTAINERID,PRODUCTID,NOTES,CREATEEMPLOYEEID,CREATEDATE) VALUES(")
                        strsql.AppendFormat("'{0}',", Guid.NewGuid.ToString)
                        strsql.AppendFormat("'{0}',", item("ContainerId"))
                        strsql.AppendFormat("'{0}',", item("ProductId"))
                        strsql.AppendFormat("'{0}',", item("Notes"))
                        strsql.AppendFormat("'{0}',", item("CreateEmployeeId"))
                        strsql.AppendFormat("{0})", "sysdate")
                    Else
                        strsql.AppendFormat("UPDATE containerbomproductattr cbd set cbd.NOTES ='{0}' where cbd.CONTAINERID='{1}' and cbd.PRODUCTID='{2}'", item("Notes"), item("ContainerId"), item("ProductId"))
                    End If
                End If
                DatabaseHelper.ExecuteSql(strsql.ToString())
            Next
        Catch ex As Exception
            SaveContainerBomProductAttribute = False
        End Try
    End Function

    ''' <summary>
    ''' 保存批次工序不加工 add by zhangrj 2022-03-30
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateStepNoShip(ByVal para As List(Of Dictionary(Of String, String))) As Boolean
        Try
            Dim sqlList As New ArrayList
            CreateStepNoShip = True
            Dim tranSql As String = <string>
                                   DECLARE nums NUMBER;
                                   BEGIN
                                      {0}
                                   END;
                                   </string>
            Dim sql = <string>
                       SELECT nvl(COUNT(*),0) INTO nums FROM containerstepnoship css WHERE css.containerid = '{1}' AND css.workflowstepid = '{2}' AND css.noship = 2 AND css.isuse=0;
                       IF nums = 0 THEN
                          --插入工序不加工数据
                          INSERT INTO containerstepnoship(CONTAINERSTEPNOSHIPID,CONTAINERID,WORKFLOWSTEPID,ISUSE,NOSHIP,EMPLOYEEID,CREATEDATE)
                          VALUES('{0}','{1}','{2}','0','2','{3}',SYSDATE);
                          --插入工序备注为不加工
                          INSERT INTO containerstepattribute (CONTAINERSTEPATTRIBUTEID,CONTAINERID,WORKFLOWSTEPID,NOTES,CREATEEMPLOYEEID,CREATEDATE) 
                          VALUES(sys_guid(),'{1}','{2}','不加工','{3}',SYSDATE);
                       END IF; 
                       {4}
                      </string>.Value
            Dim sql1 = sql
            For index = 0 To para.Count - 1
                Dim item = para(index)
                If index = para.Count - 1 Then
                    sql = ""
                End If
                sql1 = String.Format(sql1, Guid.NewGuid.ToString, item("ContainerId"), item("WorkflowStepId"), item("CreateEmployeeId"), sql)
            Next
            tranSql = String.Format(tranSql, sql1)
            DatabaseHelper.ExecuteSql(tranSql)
        Catch ex As Exception
            CreateStepNoShip = False
        End Try
    End Function

    ''' <summary>
    ''' 保存批次工序不加工 add by zhangrj 2023-09-07
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateStepNoShip(ByVal dt As DataTable) As Boolean
        Try
            Dim sqlList As New ArrayList
            CreateStepNoShip = True
            Dim tranSql As String = <string>
                                   DECLARE nums NUMBER;
                                   BEGIN
                                      {0}
                                   END;
                                   </string>
            Dim sql = <string>
                       SELECT nvl(COUNT(*),0) INTO nums FROM containerstepnoship css WHERE css.containerid = '{1}' AND css.workflowstepid = '{2}' AND css.noship = 2 AND css.isuse=0;
                       IF nums = 0 THEN
                          --插入工序不加工数据
                          INSERT INTO containerstepnoship(CONTAINERSTEPNOSHIPID,CONTAINERID,WORKFLOWSTEPID,ISUSE,NOSHIP,EMPLOYEEID,CREATEDATE)
                          VALUES('{0}','{1}','{2}','0','2','{3}',SYSDATE);
                          --插入工序备注为不加工
                          INSERT INTO containerstepattribute (CONTAINERSTEPATTRIBUTEID,CONTAINERID,WORKFLOWSTEPID,NOTES,CREATEEMPLOYEEID,CREATEDATE) 
                          VALUES(sys_guid(),'{1}','{2}','不加工','{3}',SYSDATE);
                       END IF; 
                       {4}
                      </string>.Value
            Dim sql1 = sql
            For index = 0 To dt.Rows.Count - 1
                Dim item = dt.Rows(index)
                If index = dt.Rows.Count - 1 Then
                    sql = ""
                End If
                sql1 = String.Format(sql1, Guid.NewGuid.ToString, item("ContainerId").ToString(), item("WorkflowStepId").ToString(), item("CreateEmployeeId").ToString(), sql)
            Next
            tranSql = String.Format(tranSql, sql1)
            DatabaseHelper.ExecuteSql(tranSql)
        Catch ex As Exception
            CreateStepNoShip = False
        End Try
    End Function

    ''' <summary>
    ''' 验证是否有效的不加工
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsValidStepNoShip(ByVal dt As DataTable) As uMESProcessResult
        Dim result As New uMESProcessResult
        For Each dr As DataRow In dt.Rows
            Dim sql = "SELECT 1 FROM containerstepworkreport csw WHERE csw.containerid = '{0}' AND csw.workflowstepid = '{1}' AND csw.isuse = 0"
            sql = String.Format(sql, dr("ContainerId").ToString(), dr("WorkflowStepId").ToString())
            '验证是否存在报工
            If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
                result.Result = False
                result.Message = String.Format("批次：{0}的工序{1}存在报工", dr("ContainerName").ToString(), dr("WorkflowStepName").ToString())
                Return result
                Exit Function
            End If
            'sql = String.Format(<string>
            '      SELECT 1 FROM containershiprecord csr 
            '      LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid = csr.containershiprecordid
            '      WHERE csr.containerid = '{0}' AND csrs.workflowstepid = '{1}' AND csr.isuse = 0
            '  </string>.Value, dr("ContainerId").ToString(), dr("WorkflowStepId").ToString())
            ''验证是否存在转工
            'If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
            '    result.Result = False
            '    result.Message = String.Format("批次：{0}的工序{1}存在转工", dr("ContainerName").ToString(), dr("WorkflowStepName").ToString())
            '    Return result
            '    Exit Function
            'End If
            sql = String.Format(<string>
                    SELECT 1 FROM containercommissioning cc 
                    LEFT JOIN containercommidetail cm ON cm.containercommissioningid = cc.containercommissioningid
                    WHERE cc.containerid = '{0}' AND cm.workflowstepid = '{1}' AND cc.isuse = 0 AND cm.isuse = 0
              </string>.Value, dr("ContainerId").ToString(), dr("WorkflowStepId").ToString())
            '验证是否存在外委
            If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
                result.Result = False
                result.Message = String.Format("批次：{0}的工序{1}存在外委", dr("ContainerName").ToString(), dr("WorkflowStepName").ToString())
                Return result
                Exit Function
            End If
        Next
        result.Result = True
        Return result
    End Function

    ''' <summary>
    ''' 移除批次工序不加工 add by zhangrj 2022-03-30
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RemoveStepNoShip(ByVal dt As DataTable) As Boolean
        Try
            Dim sqlList As New ArrayList
            Dim strsql As New StringBuilder
            RemoveStepNoShip = True
            For Each item As DataRow In dt.Rows
                '删除不加工数据
                strsql.Clear()
                strsql.AppendFormat("UPDATE containerstepnoship css SET css.isuse = 1  WHERE css.containerstepnoshipid ='{0}'", item("CONTAINERSTEPNOSHIPID").ToString)
                sqlList.Add(strsql.ToString)
                '同时删除工序备注中不加工备注
                strsql.Clear()
                strsql.AppendFormat("DELETE FROM containerstepattribute csa WHERE csa.containerid = '{0}' AND csa.workflowstepid='{1}' AND csa.notes = '不加工'", item("CONTAINERID").ToString, item("WORKFLOWSTEPID").ToString)
                sqlList.Add(strsql.ToString)
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
        Catch ex As Exception
            RemoveStepNoShip = False
        End Try
    End Function

    ''' <summary>
    ''' 获取批次已报工的工序列表
    ''' </summary>
    ''' <param name="sContainerId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepWorkReportList(ByVal sContainerId As String) As DataTable
        Dim strSql As String = <string>
SELECT csw.containerstepworkreportid,csw.workerid,csw.createdate,ws.workflowstepid,
s.specno,s.opdesc,csw.notes,e.fullname workername,c.containername
,DECODE(csw.checktype,'1','首件','2','序检','3','试件报工','4','返修','20','自动报工','25','发料接口报工') checktypename
 FROM containerstepworkreport csw 
 LEFT JOIN containercurrentinfo c ON c.containerid = csw.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csw.workflowstepid
left join specbase sb on sb.specbaseid = ws.specbaseid 
left join spec s on s.specid = nvl(sb.revofrcdid,ws.specid) 
LEFT JOIN employee e ON e.employeeid = csw.workerid
WHERE csw.isuse = 0 AND csw.containerid IN ({0})
ORDER BY csw.containerid,ws.sequence                                   
                              </string>
        strSql = String.Format(strSql, sContainerId)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 保存报工备注信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveStepWorkReportNotesData(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim strSql = "UPDATE containerstepworkreport csw SET csw.notes = '{0}' WHERE csw.containerstepworkreportid IN ({1})"
        strSql = String.Format(strSql, para("notes"), para("workreportIDs"))
        If DatabaseHelper.ExecuteSql(strSql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
#End Region

#Region "常用术语 add by zhangrj"
    ''' <summary>
    ''' 查询人员的角色（只包含企业下的角色）
    ''' </summary>
    ''' <param name="employeeId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRoleListByUserId(ByVal employeeId As String) As DataTable
        Dim strSql = <string>
SELECT r.roleid,r.rolename FROM employeerole er 
        LEFT JOIN employee e ON e.employeeid = er.employeeid  
        LEFT JOIN roledef r ON r.roleid = er.roleid WHERE e.employeeid = '{0}'            
                     </string>.Value
        strSql = String.Format(strSql, employeeId)
        strSql += " and r.rolename <> 'Login'"
        Dim dtRole As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
        Dim dtAllRole = m_CommonObj.GetEnterpriseRole()
        Dim qJJ As IEnumerable(Of DataRow) = dtAllRole.AsEnumerable().Intersect(dtRole.AsEnumerable(), DataRowComparer.Default)
        Return qJJ.CopyToDataTable()
    End Function
    ''' <summary>
    ''' 获取常用术语（通过角色名）
    ''' </summary>
    ''' <param name="roleName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetCommonTerm(ByVal roleName As String) As DataTable
        Dim strSql = <string>
SELECT f.factorycommontermid,f.termvalue FROM factorycommonterm f WHERE f.rolename = '{0}'                   
                     </string>.Value
        strSql = String.Format(strSql, roleName)
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function
    ''' <summary>
    ''' 添加/删除工序备注常用术语
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveStepCommonTerm(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim strsql As New StringBuilder
            If para("isDelete") Then
                strsql.Clear()
                strsql.AppendFormat("DELETE FROM factorycommonterm f WHERE f.FACTORYCOMMONTERMID in ({0})", para("factorycommontermId"))
            Else
                strsql.Clear()
                strsql.AppendLine("INSERT INTO factorycommonterm (FACTORYCOMMONTERMID,ROLENAME,TERMVALUE,CREATEEMPLOYEEID,CREATEDATE) VALUES(")
                strsql.AppendFormat("'{0}',", Guid.NewGuid.ToString)
                strsql.AppendFormat("'{0}',", para("roleName"))
                strsql.AppendFormat("'{0}',", para("termValue"))
                strsql.AppendFormat("'{0}',", para("CreateEmployeeId"))
                strsql.AppendFormat("{0})", "sysdate")
            End If
            DatabaseHelper.ExecuteSql(strsql.ToString)
            result.Result = True
            result.Message = "保存成功"
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
#End Region

#Region "叶片机加平行作业工序维护 add by zhangrj 20220823"
    ''' <summary>
    ''' 获取锻件批次列表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetForgeContainerListByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim strSql As String
            strSql = <string>
SELECT y.ypmacheextraworkid, cci.mfgordername,cci.containername,cci.containerid,cci.workflowstepname,y.pxzyspecno,y.ypmacheextraworkno FROM containercurrentinfo cci 
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN ypmacheextrawork y ON y.containerid = c.containerid
WHERE cci.containerstatus = 1 AND c.ypcontainertype = '1'
AND cci.isinstore in (-1,0)
and cci.factoryid = '{0}' 
                     </string>.Value
            strSql = String.Format(strSql, para("FactoryID"))

            If para.Keys.Contains("ScanContainerName") Then '扫描流水卡
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql += " AND ((cci.containertype <> '7' and cci.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (cci.containertype = '7' and cci.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If

            If para.Keys.Contains("ContainerName") Then '流水卡
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql += " AND cci.containername like '%" + para("ContainerName") + "%'"
                End If
            End If

            strSql += " ORDER BY cci.containerid"
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
    ''' <summary>
    ''' 保存叶片机加平行作业工序
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveYpMacheExtraWork(ByVal dt As DataTable, ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        result.Result = True
        Try
            Dim sqlList As New ArrayList
            Dim strsql As String
            For Each dr As DataRow In dt.Rows
                strsql = ""
                If String.IsNullOrWhiteSpace(dr("ypmacheextraworkid").ToString) Then '如果没有id,则添加
                    '验证是否存在平行作业单号
                    strsql = "SELECT * FROM ypmacheextrawork y WHERE y.ypmacheextraworkno = '" + para("extraOddNum") + "'"
                    Dim dt1 As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
                    If dt1.Rows.Count > 0 Then
                        result.Result = False
                        result.Message = "平行作业单号已经存在"
                        Exit For
                    End If
                    '插入数据
                    strsql = ("INSERT INTO ypmacheextrawork (YPMACHEEXTRAWORKID,PXZYSPECNO,YPMACHEEXTRAWORKNO,CONTAINERID) VALUES(sys_guid(),")
                    strsql += String.Format("'{0}',", para("pxSpecNo"))
                    strsql += String.Format("'{0}',", para("extraOddNum"))
                    strsql += String.Format("'{0}')", dr("ContainerId"))
                    sqlList.Add(strsql)
                Else
                    '验证除了本条数据外是否存在平行作业单号
                    strsql = "SELECT * FROM ypmacheextrawork y WHERE y.ypmacheextraworkno = '" + para("extraOddNum") + "' and y.ypmacheextraworkid <> '" + dr("ypmacheextraworkid").ToString + "'"
                    Dim dt1 As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
                    If dt1.Rows.Count > 0 Then
                        result.Result = False
                        result.Message = "平行作业单号已经存在"
                        Exit For
                    End If
                    '更新数据
                    strsql = "UPDATE ypmacheextrawork y set "
                    If Not String.IsNullOrWhiteSpace(para("pxSpecNo").ToString) Then
                        strsql += String.Format("y.PXZYSPECNO ='{0}',", para("pxSpecNo"))
                    End If
                    If Not String.IsNullOrWhiteSpace(para("extraOddNum").ToString) Then
                        strsql += String.Format("y.YPMACHEEXTRAWORKNO ='{0}'", para("extraOddNum"))
                    End If
                    strsql = strsql.Trim(",")
                    strsql += String.Format(" where y.ypmacheextraworkid = '{0}'", dr("ypmacheextraworkid"))
                    sqlList.Add(strsql)
                End If
            Next
            If result.Result Then
                DatabaseHelper.ExecuteSqlTran(sqlList) '更新记录'
                result.Result = True
                result.Message = "保存成功"
            End If
            Return result
        Catch ex As Exception
            result.Result = False
            result.Message = "保存出错，原因：" + ex.Message
            Return result
        End Try
    End Function

    ''' <summary>
    ''' 获取机加暂停批次（平行作业工序暂停）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMacheHoldContainerList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            Dim strSql As String
            strSql = <string>
SELECT Distinct c.containerid,c.containername,c.mfgordername,c.workflowstepname,c.productid,c.productname,
u1.attributevalue pxzyspecno,u2.attributevalue pxzyoddno,c.qty,o.filename,o.objectdocinfoid
--DECODE(o.objectdocinfoid, NULL,'否','是') isupload
FROM containercurrentinfo c
LEFT JOIN container cc ON cc.containerid = c.containerid
LEFT JOIN Userattribute u1 ON u1.parentid = c.containerid AND u1.userattributename = '平行作业工序'
LEFT JOIN Userattribute u2 ON u2.parentid = c.containerid AND u2.userattributename = '平行作业单号'
LEFT JOIN Userattribute u3 ON u3.parentid = c.containerid AND u3.userattributename = '器材检验'
LEFT JOIN objectdocinfo o ON o.parentid = c.containerid
WHERE c.ishold =1 AND cc.ypcontainertype = '2' AND u3.attributevalue IS NULL AND u1.attributevalue IS NOT NULL AND u2.attributevalue IS NOT NULL
AND c.containerstatus = 1 AND c.factoryid = '{0}'
                     </string>.Value
            strSql = String.Format(strSql, para("FactoryID"))

            If para.Keys.Contains("ScanContainerName") Then '扫描流水卡
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql += " AND ((c.containertype <> '7' and c.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (c.containertype = '7' and c.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If

            If para.Keys.Contains("ContainerName") Then '流水卡
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql += " AND c.containername like '%" + para("ContainerName") + "%'"
                End If
            End If

            strSql += " ORDER BY c.containerid"
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
    ''' <summary>
    ''' 释放机加平行作业工序暂停
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="employeeID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReleaseMacheHoldContainer(ByVal dt As DataTable, ByVal employeeID As String) As uMESProcessResult
        Dim result As New uMESProcessResult
        Try
            '验证是否上传附件
            If Not IsUploadFileByHoldContainer(dt) Then
                result.Result = False
                result.Message = "批次存在未上传文件"
                Return result
                Exit Function
            End If
            Dim strSql = ""
            Dim sqlList As New ArrayList
            For Each dr As DataRow In dt.Rows
                '更新例外放行表
                strSql = "UPDATE ypmacheextrawork y SET y.releaseempid = '{0}', y.releasedate = sysdate WHERE y.ypmacheextraworkno = '{1}'"
                strSql = String.Format(strSql, employeeID, dr("pxzyoddno").ToString)
                sqlList.Add(strSql)
                '更新批次属性
                strSql = "UPDATE Userattribute u SET u.attributevalue = '1' WHERE u.parentid = '" + dr("containerid").ToString + "' AND u.userattributename = '器材检验'"
                sqlList.Add(strSql)
                '更新批次表暂停
                strSql = "UPDATE containercurrentinfo cu SET cu.ishold = '0' WHERE cu.containerid = '" + dr("containerid").ToString + "'"
                sqlList.Add(strSql)
                '插入批次释放记录
                strSql = "INSERT INTO OperContainerRecord(containerid,containername,productid,productname,operatorid,operreason,qty,createdate,opertype) VALUES("
                strSql += "'" + dr("containerid").ToString + "',"
                strSql += "'" + dr("containername").ToString + "',"
                strSql += "'" + dr("productid").ToString + "',"
                strSql += "'" + dr("productname").ToString + "',"
                strSql += "'" + employeeID + "',"
                strSql += "'平行作业工序释放',"
                strSql += "'" + dr("qty").ToString + "',"
                strSql += "sysdate,"
                strSql += "'3')"
                sqlList.Add(strSql)
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Result = True
            result.Message = "释放成功"
            Return result
        Catch ex As Exception
            result.Result = False
            result.Message = "释放出错，原因：" + ex.Message
            Return result
        End Try
    End Function

    ''' <summary>
    ''' 验证暂停批次是否上传了文件
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsUploadFileByHoldContainer(ByVal dt As DataTable) As Boolean
        Dim isValid = True
        For Each dr As DataRow In dt.Rows
            Dim strSql = "SELECT * FROM objectdocinfo o WHERE o.parentid = '" + dr("ContainerId") + "'"
            Dim dtr As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            If dtr.Rows.Count = 0 Then
                isValid = False
                Exit For
            End If
        Next
        Return isValid
    End Function

    ' ''' <summary>
    ' ''' 插入附件
    ' ''' </summary>
    ' ''' <param name="para"></param>
    ' ''' <returns></returns>
    ' ''' <remarks></remarks>
    'Public Function SaveFileInfo(ByVal para As Dictionary(Of String, String)) As Boolean
    '    Try
    '        Dim strSql = "SELECT * FROM objectdocinfo o WHERE o.parentid = '" + para("parentId").ToString + "'"
    '        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
    '        strSql = ""
    '        If dt.Rows.Count > 0 Then
    '            strSql = "UPDATE objectdocinfo o SET o.filename = '{0}', o.filepath='{1}' where o.OBJECTDOCINFOID = '{2}'"
    '            strSql = String.Format(strSql, para("fileName").ToString, para("filePath").ToString, dt.Rows(0)("OBJECTDOCINFOID").ToString)
    '        Else
    '            strSql = "INSERT INTO objectdocinfo(OBJECTDOCINFOID,parentid,filename,filepath) VALUES("
    '            strSql += "'" + Guid.NewGuid.ToString + "',"
    '            strSql += "'" + para("parentId").ToString + "',"
    '            strSql += "'" + para("fileName").ToString + "',"
    '            strSql += "'" + para("filePath").ToString + "')"
    '        End If
    '        DatabaseHelper.ExecuteSql(strSql)
    '        Return True
    '    Catch ex As Exception
    '        Return False
    '    End Try
    'End Function
#End Region

End Class
